Import Text date ranges

[jeremiah]

When importing a text file that has a date range (04/23/07 - 04/30/07) as a
heading in it. I created a query that separates the dates into 2 new fields
by using...Start Date: First(Left([SHIFT],8)) & End Date:
First(Right([SHIFT],8)) and puts those dates into the new fields for each
record following that heading.

Each time I import the text and append it to the previous table there will
be a new date range, which may be row 10 or row 100, I never know which line
the date range will be in. I need to be able to use the New dates in those
records, not the dates in the first line of the table. Any ideas?

 

[John Nurick]

Can you give an example of what the text file contains?

When importing a text file that has a date range (04/23/07 - 04/30/07) as a
heading in it. I created a query that separates the dates into 2 new fields
by using...Start Date: First(Left([SHIFT],8)) & End Date:
First(Right([SHIFT],8)) and puts those dates into the new fields for each
record following that heading.

Each time I import the text and append it to the previous table there will
be a new date range, which may be row 10 or row 100, I never know which line
the date range will be in. I need to be able to use the New dates in those
records, not the dates in the first line of the table. Any ideas?

 

[jeremiah]

Below is the first few lines of data - I need to be able to associate each
line of data with the correct date range - I import it delimited by
semicolons but the date headings do not follow the same format. I need to be
able to query and summarize by date.

02/25/07-03/03/07
;000.9122;NAME NOT AVAILABLE ;HPS
;000.9122;NAME NOT AVAILABLE
04/15/07-04/21/07
; 0030178;JOHN DOE;
; 0030178;NAME NOT AVAILABLE
;B0009530;NAME NOT AVAILABLE
;B0009530;NAME NOT AVAILABLE

Can you give an example of what the text file contains?

When importing a text file that has a date range (04/23/07 - 04/30/07) as a
heading in it. I created a query that separates the dates into 2 new fields
by using...Start Date: First(Left([SHIFT],8)) & End Date:
First(Right([SHIFT],8)) and puts those dates into the new fields for each
record following that heading.

Each time I import the text and append it to the previous table there will
be a new date range, which may be row 10 or row 100, I never know which line
the date range will be in. I need to be able to use the New dates in those
records, not the dates in the first line of the table. Any ideas?

 

[John Nurick]

Hi Jeremiah,

I'd do this by writing code to pre-process the text file before
importing, converting it into a regular text file with all lines having
the same fields.

VBA-like pseudocode:

Open file for input as #fIn
Open new text file for output as #fOut

Do Until EOF(fIn)

Line Input #fIn, strLine 'read line

If Left(strLine, 1) <> " " Then 'it's a date header
StartDate = blah 'extract dates from line
EndDate = blah

Else 'strLine contains an ordinary record
Trim leading and trailing spaces

strLine = StartDate & ";" & EndDate & strLine

'maybe at this point check and adjust
'the number of fields in the output line

'maybe also replace ; with , to convert fOut
'into a standard CSV file that Access can
'import without an import specification

Print #fOut, strLine
End If
Loop
Close #fIn
Close #fOut

The comment about adjusting the number of fields is because I noticed
that the records in your sample data don't have a consistent number of
fields. It might be worth appending a delimiter to the end of each line
that doesn't have the final field ("HPS" in your sample).

I usually use Perl for this sort of thing because it has much more
powerful text file and string-handling functions than VBA. Here's a
working Perl script that processes your test data:

use strict;
my $start; #start date
my $end; #end date
my $line;
my $fieldsNeeded = 5; #number of fields that should be output
my $fieldsToAdd; #number of empty fields to be appended

while (<>) { #read file line by line
chomp;
if (m/^\s+/) { #line beginning with space => an ordinary record
$_ =~ s/^\s+//; #trim leading space
$_ =~ s/\s+$//; #trim trailing space
$line = "$start;$end$_"; #prepend dates
$fieldsToAdd = $fieldsNeeded - split(/;/, $line);
$line .= ';' while (--$fieldsToAdd >= 0) ; #add fields if needed
$line ~= s/;/,/g; #change ';' to ','
print "$line\n"; #output line
} else { #heading line with dates
($start, $end) = split /-/ ;
}
}

Below is the first few lines of data - I need to be able to associate each
line of data with the correct date range - I import it delimited by
semicolons but the date headings do not follow the same format. I need to be
able to query and summarize by date.

02/25/07-03/03/07
;000.9122;NAME NOT AVAILABLE ;HPS
;000.9122;NAME NOT AVAILABLE
04/15/07-04/21/07
; 0030178;JOHN DOE;
; 0030178;NAME NOT AVAILABLE
;B0009530;NAME NOT AVAILABLE
;B0009530;NAME NOT AVAILABLE

Can you give an example of what the text file contains?

When importing a text file that has a date range (04/23/07 - 04/30/07) as a
heading in it. I created a query that separates the dates into 2 new fields
by using...Start Date: First(Left([SHIFT],8)) & End Date:
First(Right([SHIFT],8)) and puts those dates into the new fields for each
record following that heading.

Each time I import the text and append it to the previous table there will
be a new date range, which may be row 10 or row 100, I never know which line
the date range will be in. I need to be able to use the New dates in those
records, not the dates in the first line of the table. Any ideas?

 

[jeremiah]

Sorry, I'm not a developer. I'm self taught but my confidence level in VBA
is not great.

Am I putting this as an OpenModule directly ahead of my txt transfer in the
macro or is done outside of Access?

Hi Jeremiah,

I'd do this by writing code to pre-process the text file before
importing, converting it into a regular text file with all lines having
the same fields.

VBA-like pseudocode:

Open file for input as #fIn
Open new text file for output as #fOut

Do Until EOF(fIn)

Line Input #fIn, strLine 'read line

If Left(strLine, 1) <> " " Then 'it's a date header
StartDate = blah 'extract dates from line
EndDate = blah

Else 'strLine contains an ordinary record
Trim leading and trailing spaces

strLine = StartDate & ";" & EndDate & strLine

'maybe at this point check and adjust
'the number of fields in the output line

'maybe also replace ; with , to convert fOut
'into a standard CSV file that Access can
'import without an import specification

Print #fOut, strLine
End If
Loop
Close #fIn
Close #fOut

The comment about adjusting the number of fields is because I noticed
that the records in your sample data don't have a consistent number of
fields. It might be worth appending a delimiter to the end of each line
that doesn't have the final field ("HPS" in your sample).

I usually use Perl for this sort of thing because it has much more
powerful text file and string-handling functions than VBA. Here's a
working Perl script that processes your test data:

use strict;
my $start; #start date
my $end; #end date
my $line;
my $fieldsNeeded = 5; #number of fields that should be output
my $fieldsToAdd; #number of empty fields to be appended

while (<>) { #read file line by line
chomp;
if (m/^\s+/) { #line beginning with space => an ordinary record
$_ =~ s/^\s+//; #trim leading space
$_ =~ s/\s+$//; #trim trailing space
$line = "$start;$end$_"; #prepend dates
$fieldsToAdd = $fieldsNeeded - split(/;/, $line);
$line .= ';' while (--$fieldsToAdd >= 0) ; #add fields if needed
$line ~= s/;/,/g; #change ';' to ','
print "$line\n"; #output line
} else { #heading line with dates
($start, $end) = split /-/ ;
}
}

Below is the first few lines of data - I need to be able to associate each
line of data with the correct date range - I import it delimited by
semicolons but the date headings do not follow the same format. I need to be
able to query and summarize by date.

02/25/07-03/03/07
;000.9122;NAME NOT AVAILABLE ;HPS
;000.9122;NAME NOT AVAILABLE
04/15/07-04/21/07
; 0030178;JOHN DOE;
; 0030178;NAME NOT AVAILABLE
;B0009530;NAME NOT AVAILABLE
;B0009530;NAME NOT AVAILABLE

Can you give an example of what the text file contains?

On Thu, 26 Apr 2007 09:14:03 -0700, jeremiah

When importing a text file that has a date range (04/23/07 - 04/30/07) as a
heading in it. I created a query that separates the dates into 2 new fields
by using...Start Date: First(Left([SHIFT],8)) & End Date:
First(Right([SHIFT],8)) and puts those dates into the new fields for each
record following that heading.

Each time I import the text and append it to the previous table there will
be a new date range, which may be row 10 or row 100, I never know which line
the date range will be in. I need to be able to use the New dates in those
records, not the dates in the first line of the table. Any ideas?

 

[John Nurick]

Your confidence level will have improved by the time we've got this
working! As for the specific question:

1) If you want it to work as a seamless process within Access, you'll
probably need to use VBA throughout; speaking for myself I wouldn't
bother trying to do it with a macro.

Start by fleshing out the pseudocode I posted into a working VBA Sub
procedure in a module in your database. Hard-code the file names for
simplicity. (You can probably use a fixed name and location for the
output file, which is only needed until you have used TransferText to
import the data.)

Once that's working on your test files, add a DoCmd.TransferText
statement that imports the data from the regularised text file you have
just created. Next you can add more refinements. For instance, if you
want to put up the standard File|Open dialog so the user can select the
file, see www.mvps.org/access/api/api0001.htm.

Finally, add

Sorry, I'm not a developer. I'm self taught but my confidence level in VBA
is not great.

Am I putting this as an OpenModule directly ahead of my txt transfer in the
macro or is done outside of Access?

Hi Jeremiah,

I'd do this by writing code to pre-process the text file before
importing, converting it into a regular text file with all lines having
the same fields.

VBA-like pseudocode:

Open file for input as #fIn
Open new text file for output as #fOut

Do Until EOF(fIn)

Line Input #fIn, strLine 'read line

If Left(strLine, 1) <> " " Then 'it's a date header
StartDate = blah 'extract dates from line
EndDate = blah

Else 'strLine contains an ordinary record
Trim leading and trailing spaces

strLine = StartDate & ";" & EndDate & strLine

'maybe at this point check and adjust
'the number of fields in the output line

'maybe also replace ; with , to convert fOut
'into a standard CSV file that Access can
'import without an import specification

Print #fOut, strLine
End If
Loop
Close #fIn
Close #fOut

The comment about adjusting the number of fields is because I noticed
that the records in your sample data don't have a consistent number of
fields. It might be worth appending a delimiter to the end of each line
that doesn't have the final field ("HPS" in your sample).

I usually use Perl for this sort of thing because it has much more
powerful text file and string-handling functions than VBA. Here's a
working Perl script that processes your test data:

use strict;
my $start; #start date
my $end; #end date
my $line;
my $fieldsNeeded = 5; #number of fields that should be output
my $fieldsToAdd; #number of empty fields to be appended

while (<>) { #read file line by line
chomp;
if (m/^\s+/) { #line beginning with space => an ordinary record
$_ =~ s/^\s+//; #trim leading space
$_ =~ s/\s+$//; #trim trailing space
$line = "$start;$end$_"; #prepend dates
$fieldsToAdd = $fieldsNeeded - split(/;/, $line);
$line .= ';' while (--$fieldsToAdd >= 0) ; #add fields if needed
$line ~= s/;/,/g; #change ';' to ','
print "$line\n"; #output line
} else { #heading line with dates
($start, $end) = split /-/ ;
}
}

Below is the first few lines of data - I need to be able to associate each
line of data with the correct date range - I import it delimited by
semicolons but the date headings do not follow the same format. I need to be
able to query and summarize by date.

02/25/07-03/03/07
;000.9122;NAME NOT AVAILABLE ;HPS
;000.9122;NAME NOT AVAILABLE
04/15/07-04/21/07
; 0030178;JOHN DOE;
; 0030178;NAME NOT AVAILABLE
;B0009530;NAME NOT AVAILABLE
;B0009530;NAME NOT AVAILABLE

:

Can you give an example of what the text file contains?

On Thu, 26 Apr 2007 09:14:03 -0700, jeremiah

When importing a text file that has a date range (04/23/07 - 04/30/07) as a
heading in it. I created a query that separates the dates into 2 new fields
by using...Start Date: First(Left([SHIFT],8)) & End Date:
First(Right([SHIFT],8)) and puts those dates into the new fields for each
record following that heading.

Each time I import the text and append it to the previous table there will
be a new date range, which may be row 10 or row 100, I never know which line
the date range will be in. I need to be able to use the New dates in those
records, not the dates in the first line of the table. Any ideas?

 

[jeremiah]

Thanks, this gives me a good idea of what I need to do, although I am getting
a run-time error because of a bad file name in the very first step...
Public Sub OpenTxtFile()
Open "C:\PUMSSUMOPER2.txt" For Input As #fIn
Open "C:\PUMSSUMOPER3.txt" For Output As #fOut

I'm not off to a very good start, do the input and output files have to
reside in the same location as my database?

Your confidence level will have improved by the time we've got this
working! As for the specific question:

1) If you want it to work as a seamless process within Access, you'll
probably need to use VBA throughout; speaking for myself I wouldn't
bother trying to do it with a macro.

Start by fleshing out the pseudocode I posted into a working VBA Sub
procedure in a module in your database. Hard-code the file names for
simplicity. (You can probably use a fixed name and location for the
output file, which is only needed until you have used TransferText to
import the data.)

Once that's working on your test files, add a DoCmd.TransferText
statement that imports the data from the regularised text file you have
just created. Next you can add more refinements. For instance, if you
want to put up the standard File|Open dialog so the user can select the
file, see www.mvps.org/access/api/api0001.htm.

Finally, add

Sorry, I'm not a developer. I'm self taught but my confidence level in VBA
is not great.

Am I putting this as an OpenModule directly ahead of my txt transfer in the
macro or is done outside of Access?

Hi Jeremiah,

I'd do this by writing code to pre-process the text file before
importing, converting it into a regular text file with all lines having
the same fields.

VBA-like pseudocode:

Open file for input as #fIn
Open new text file for output as #fOut

Do Until EOF(fIn)

Line Input #fIn, strLine 'read line

If Left(strLine, 1) <> " " Then 'it's a date header
StartDate = blah 'extract dates from line
EndDate = blah

Else 'strLine contains an ordinary record
Trim leading and trailing spaces

strLine = StartDate & ";" & EndDate & strLine

'maybe at this point check and adjust
'the number of fields in the output line

'maybe also replace ; with , to convert fOut
'into a standard CSV file that Access can
'import without an import specification

Print #fOut, strLine
End If
Loop
Close #fIn
Close #fOut

The comment about adjusting the number of fields is because I noticed
that the records in your sample data don't have a consistent number of
fields. It might be worth appending a delimiter to the end of each line
that doesn't have the final field ("HPS" in your sample).

I usually use Perl for this sort of thing because it has much more
powerful text file and string-handling functions than VBA. Here's a
working Perl script that processes your test data:

use strict;
my $start; #start date
my $end; #end date
my $line;
my $fieldsNeeded = 5; #number of fields that should be output
my $fieldsToAdd; #number of empty fields to be appended

while (<>) { #read file line by line
chomp;
if (m/^\s+/) { #line beginning with space => an ordinary record
$_ =~ s/^\s+//; #trim leading space
$_ =~ s/\s+$//; #trim trailing space
$line = "$start;$end$_"; #prepend dates
$fieldsToAdd = $fieldsNeeded - split(/;/, $line);
$line .= ';' while (--$fieldsToAdd >= 0) ; #add fields if needed
$line ~= s/;/,/g; #change ';' to ','
print "$line\n"; #output line
} else { #heading line with dates
($start, $end) = split /-/ ;
}
}


On Fri, 27 Apr 2007 05:42:02 -0700, jeremiah

Below is the first few lines of data - I need to be able to associate each
line of data with the correct date range - I import it delimited by
semicolons but the date headings do not follow the same format. I need to be
able to query and summarize by date.

02/25/07-03/03/07
;000.9122;NAME NOT AVAILABLE ;HPS
;000.9122;NAME NOT AVAILABLE
04/15/07-04/21/07
; 0030178;JOHN DOE;
; 0030178;NAME NOT AVAILABLE
;B0009530;NAME NOT AVAILABLE
;B0009530;NAME NOT AVAILABLE

:

Can you give an example of what the text file contains?

On Thu, 26 Apr 2007 09:14:03 -0700, jeremiah

When importing a text file that has a date range (04/23/07 - 04/30/07) as a
heading in it. I created a query that separates the dates into 2 new fields
by using...Start Date: First(Left([SHIFT],8)) & End Date:
First(Right([SHIFT],8)) and puts those dates into the new fields for each
record following that heading.

Each time I import the text and append it to the previous table there will
be a new date range, which may be row 10 or row 100, I never know which line
the date range will be in. I need to be able to use the New dates in those
records, not the dates in the first line of the table. Any ideas?