Import Issue

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

Hi,

Access 2000, XP Home

I have a task each month to bring a CSV file into Access, the CSV file is in
a format which I'm not sure I can automate the input process.

The first row of the file has two entries, firstly a Site ID and then a
Date, both ID and date applies to each record in the file. The second and
third line are not required. The forth line now takes on nearly the correct
format albeit it there's no date in the record. However, there are other
entries in the file where there are repeats of the second and third lines
which are not required, there is also a footer which shows the number of
records.

Currently, each month I open the file in Excel, add a column and put in the
date, I then sort the records to get the unwanted rows together and then
delete, the file is saved in Excel then imported into Access. This is
rather long winded and I would like to just import direct into Access.

My question is should I ask the originator of this file to change the format
or can I accomplish in Access the import process as the file is?

Any pointers most welcome.

Rob
512,19062004
512,01,FPRICE CATEGORIES,
512,01,--------------,
512,01,- Full Price,1400
512,01, - IncrValue,60
512,01, - Total,1460
512,02,FPRICE CATEGORIES,
512,02,--------------,
512,01,- Full Price,1230
512,01, - IncrValue,35
512,01, - Total,1265
000011
 
I know how I'd get started with this, but I can't really
tell from your post which rows or fields you want to appear
in the final table. And I 'd expect to experiment a bit to
get the required result anyway.

To start, assuming that none of the rows exceed 255 chars in
length, I'd import each whole line into a text field in a
new table. I wouldn't bother with using the csv option in
the import wizard, just import them as a fixed width larger
than the longest row, say 254 chars.

Then I'd knock up some update queries to manipulate and
split up that imported data into other columns in the same
table. Using functions like instr, mid, len and so on to
pull out the bits I want and bung them into appropriate
columns.

Once you've got it split up in this temp import table you
can run an append query to put the sanitised fields into
your desired table.

It could be done with vba from the off, but it's usually
much easier to experiment with queries, then note down what
works. Write a flow chart perhaps. Then you could try to
automate it with vba code and recordsets because now you've
got the string manipulation functions working the way you
want them.

Good luck.
 
Back
Top