CSV Import - Blank columns causing errors

I have had a good search around the forum, but can't find a solution to this one. Any help, or links to a solution would be appreciated.

I am using code to import an Excel CSV file into a pre-formatted table.

My problem is the csv file is automatically generated each day, and has varying amounts of blank columns depending on the orders being received by my customer.

I could have six of seven blank columns between the first section of date, and the last section.

When I run the code through a macro, I get the error "output destination error "noname"."

I entered a column on my table called "noname", which resolved the first blank column, but as it tries to import the second blank column, I get the error "Duplicate output destination error "noname"."

I need to use column headers, so how can I tell the system to ignore any blank columns?

Thanks in Advance.

Adam.

Function import_test()
On Error GoTo import_test_Err

DoCmd.TransferText acImportDelim, "", "imported_ordersa", "c:\rx002203.csv", True



import_test_Exit:
Exit Function

import_test_Err:
MsgBox Error$
Resume import_test_Exit

End Function

Click to expand...

The last True in the docmd line means the first row has fieldnames changing it to False should do the trick. For more info lookup the help on TransferText.

Thanks, but I have tried this.

I need to add "true", as I need to bring in the fieldnames so I can match the correctl orders with the correct products. This causes the problems on the blank columns.

I can't find anything in the help on transfertext which explains what to do about blank columns.


Adam.

For a moment I thought I had a solution.

I set up an Import Specification which solved the problem with the blank columns, and imported without error warnings.

However, the problem with the Import Specification is that although the products I'm importing have the same name in each file, they are not always all present, and so the products in the file are not always in the same order.

Although I state "True" in my code - to import headings - it is ignoring the headings, and assuming the file is always in the same order.

My orders are now being allocated to the wrong products, and being imported based on the order they are in the file, rather than based on the first row, which should be the field headings.


please...... anyone?????

G’day Adam.

If you could post two different examples of the data files and a copy of the table you wish to put the data in then someone may be able to help.

Regards,
Chris.

Files are attached.

The table I'm importing into is in DB1, and the two RX numbered files contain the orders I'm importing, including the blank columns.


Adam

G’day Adam.

Well first up, thanks for the data you have supplied.

If others choose to import this data then they are welcome but for me the answer is no.
The main reason is that you seem to be thinking horizontally rather than vertically.

If I read the situation correctly, there may not be fields in the destination table that match fields in the imported files. OK, no real problem with that, simply make the field names in the destination table to comply with the field in the imported files.

Consider this…“Organic Diced Carrot & Swede 12*350g”
If we do not have an exact match for that data we are supposed to create a new field in the destination table???

Every new vegetable, every new package size, every new spelling mistake would lead to a new field in the destination table but you only have 255 fields to play with.

It’s a spreadsheet, so why import a spreadsheet into Access…simply use it in Excel by double clicking on it, no code required.

In Access you would have something like a Customer Order Table on the one side.
On the many side of the Customer Order Table you would have something like an Order Details Table.
Within the Order Details Table you would have something like an OrderDescription field that would contain data like “Organic Diced Carrot & Swede 12*350g”.

“Organic Diced Carrot & Swede 12*350g” is not a new field in a table, it can’t be, you will run out of fields based on a description. “Organic Diced Carrot & Swede 12*350g” is a description in a new record in an Order Details Table.

Time to start thinking vertically and not horizontally.

Regards,
Chris.

Hi Chris.

Thanks for the reply - and how's the weather in the South Hem - we have Snow in Scotland!

I have thought this through in various formats, but I'm hampered by the way the CSV file is presented to me (horizontally), and the way I want to present it (vertically).

I am doing this within Access - I didn't include the full database, only the code for the import - and have no problems once the info is in Access. As usual with these things, I am tring to set up a fool-proof system for the numpties that work here!

I think I have confused you slightly with my problem.

The product description never changes (pack size and weight is always the same). Getting the columns with descriptions into my table using code is fine, and everything works after that. If I do a manual "Get Data" it works no problem.

My problem is the blank columns between data when I try to use the code.

The order number and depot date are always in columns "Q" and "R", and depending on the number of products in the order, there are varying number of blank columns.

When I try to use code without an import specification I get errors about "noname" fields.

When I try to use code with an import specification, it ignores the row headings (even though I have stated "True" at the end of the code) and assumes the products are always in the same columns - which is not the case if a couple of products are missing from the orders.

I am looking for something to write into the code which tells it to ignore anything with blank columns, and only import columns with row headings.

If I can do this, I can get on with life.


Help and advice is most appreciated, enjoy the sun!


Adam.