importing text file question

[Tcs]

I'm trying to import a text file containing file information, such as:

12/24/2005 01:00p 3,400,157,210 Mr. Magoo's-2005-12-24-0.mpg
11/10/2005 07:00p 3,400,249,370 Off-Shore Oil-2005-11-10-0.mpg

I created this file with the command:

dir > MyTextFile.txt

then I removed the extraneous lines.

1.) I'm having a problem with Access (2k) importing this data. It
gets the date and name okay, but the time and size it doesn't like. If
I open the table manually, after my importing attempt, I can enter the
time with just the "a" or "p", rather than "am" or "pm", and Access
does just fine. Likewise I can enter the filesize with imbedded
commas. I've tried adjusting the columns of data to include a
leading/trailing space, or no space. Nothing seems to work. Why does
Access not like the time and filesize from my text file? What do I
have to do to fix it?

2.) Is it possible to automate this with VBA? If so, could you
possibly push me in the right direction? I know I can use VBA to read
the file and extract the data, but how would I create the file? Or is
there some *better* way to create the info I'm after? API call(s)?

Thanks in advance,

Tom

 

[John Nurick]

Hi Tom,

There are several ways to go. By the way, you can use the /-C switch in
your DIR command to list file sizes without the commas. (And if you have
grep on your system you can use
DIR /-C | grep --regexp="^\w" > myfile.txt
to dispose of the extraneous lines.)


1) If you want to parse a file redirected from DIR, do it in two stages.
First, temporarily import or link the text file with a specification
that defines the timestamp (e.g. "12/24/2005 01:00p") as a single text
field. If you're not using /-C, define the file size as a text field.

Then use type conversion functions in an append query to move the data
into your permanent table.

CDate() will convert the timestamps into Access date/time values
provided they are in the same format as your system short date, but
you'll have problems if they're different. (In that case, define
separate fields in your import specification for day, month, year and
time, and reassemble them in the query with an expression like this:

DateSerial([TheYear], [TheMonth], [TheDay]) + CDate([TheTime])

CLng() will convert strings like "123,456,789" into the numbers like
123456789. Again, there are potential issues with different regional
settings, which are avoided if you use /-C

2) Work entirely within VBA, using Dir() to iterate through the files in
your folder and FileSize() and FileDateTime() to get the information.

3) Use the FileSystemObject object. This lets you distinguish between
Created, LastModified and so on.

 

[Kevin K. Sullivan]

Watch out for numbers over 2 billion. They are two big to store in a
Long and will cause overflow errors. Use a Double instead, as well as
the CDbl() to convert text to number.

HTH,

Kevin

Hi Tom,

There are several ways to go. By the way, you can use the /-C switch in
your DIR command to list file sizes without the commas. (And if you have
grep on your system you can use
DIR /-C | grep --regexp="^\w" > myfile.txt
to dispose of the extraneous lines.)


1) If you want to parse a file redirected from DIR, do it in two stages.
First, temporarily import or link the text file with a specification
that defines the timestamp (e.g. "12/24/2005 01:00p") as a single text
field. If you're not using /-C, define the file size as a text field.

Then use type conversion functions in an append query to move the data
into your permanent table.

CDate() will convert the timestamps into Access date/time values
provided they are in the same format as your system short date, but
you'll have problems if they're different. (In that case, define
separate fields in your import specification for day, month, year and
time, and reassemble them in the query with an expression like this:

DateSerial([TheYear], [TheMonth], [TheDay]) + CDate([TheTime])

CLng() will convert strings like "123,456,789" into the numbers like
123456789. Again, there are potential issues with different regional
settings, which are avoided if you use /-C

2) Work entirely within VBA, using Dir() to iterate through the files in
your folder and FileSize() and FileDateTime() to get the information.

3) Use the FileSystemObject object. This lets you distinguish between
Created, LastModified and so on.

I'm trying to import a text file containing file information, such as:

12/24/2005 01:00p 3,400,157,210 Mr. Magoo's-2005-12-24-0.mpg
11/10/2005 07:00p 3,400,249,370 Off-Shore Oil-2005-11-10-0.mpg

I created this file with the command:

dir > MyTextFile.txt

then I removed the extraneous lines.

1.) I'm having a problem with Access (2k) importing this data. It
gets the date and name okay, but the time and size it doesn't like. If
I open the table manually, after my importing attempt, I can enter the
time with just the "a" or "p", rather than "am" or "pm", and Access
does just fine. Likewise I can enter the filesize with imbedded
commas. I've tried adjusting the columns of data to include a
leading/trailing space, or no space. Nothing seems to work. Why does
Access not like the time and filesize from my text file? What do I
have to do to fix it?

2.) Is it possible to automate this with VBA? If so, could you
possibly push me in the right direction? I know I can use VBA to read
the file and extract the data, but how would I create the file? Or is
there some *better* way to create the info I'm after? API call(s)?

Thanks in advance,

Tom