Import text file

[Basil]

Hiya,

I have a few queries, I'll chuck 'em all down - any help
would be really appreciated.

1. If importing a CSV file, is there a way that I can get
it to only import from row 4 onwards - the headers are on
row 3, but I'm happy to ignore them and put them in the
script.

2. It would be really useful for me to be able to record
the date of import. Is it possible to have a table with
two fields - 'import file' and 'most-recent import date'.
I would populate the 'import file' field myself but
crucially, is there a way that after import I can get
Access to look for the file name (saved in a public dim)
in this table and populate the date field with the date at
the time?

3. I can't seem to get Access to convert some data to a
time. the data comes like this:

Time
937
1442
622
1854
124
02 (this is 00:02)etc..

None of the rows seem to work (I have deleted the .
seperator in the script).

Any ideas on any of the 3 would really help, many thanks.

Basil

 

[John Nurick]

Hiya,

I have a few queries, I'll chuck 'em all down - any help
would be really appreciated.

1. If importing a CSV file, is there a way that I can get
it to only import from row 4 onwards - the headers are on
row 3, but I'm happy to ignore them and put them in the
script.

There are two ways. One is to write code that reads the file a line at a
time and - starting with line 3 or line 4 - writes them to a new
textfile which is then imported. The other is to write code that reads
the file a line at a time and - starting with line 3 or line 4 - parse
each line into fields and appends them to the table.

2. It would be really useful for me to be able to record
the date of import. Is it possible to have a table with
two fields - 'import file' and 'most-recent import date'.
I would populate the 'import file' field myself but
crucially, is there a way that after import I can get
Access to look for the file name (saved in a public dim)
in this table and populate the date field with the date at
the time?

If you're not creating this record until the file has been imported, all
you need to do is to set the default value of the DateStamp field to
Date()or Now(). Otherwise - i.e. if there's already a record containing
the right filename, you just need to build and run a little update
query, along these lines:

Dim dbD as DAO.Database
Dim strSQL as String

strSQL = "UPDATE TheTable SET DateStamp = " _
& Format(Now(), "\#mm/dd/yyyy hh:nn:ss\#") _
& " WHERE FileName = '" & strFileName & "';"
Set dbD = CurrentDB()
dbD.Execute(strSQL, dbFailOnError)
Set dbD = Nothing

3. I can't seem to get Access to convert some data to a
time. the data comes like this:

Time
937
1442
622
1854
124
02 (this is 00:02)etc..

None of the rows seem to work (I have deleted the .
seperator in the script).

If I read you right 02 is two minutes after midnight, 937 is 09:37:00,
1442 is 14:42:00. Import this to a number field (Long or Integer). Then
add a date/time field to the table and use an update query to update it
to

cDate(Format([TheNumberField], "00:00"))

ALternatively, import to a number field in a temporary "staging" table,
and use that expression in an append query when you move your data into
your "permanent" table.


John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.