need to convert a CSV to an XLS for smooth import into Access table

[greyoxide]

Every month we have to process a very poorly formatted CSV file for use in reporting. This CSV has commas in some of its values (ex. column1, 34,000, column3). From my perspective this is a retarded export format, I think you should let the end user's program format the values. Unfortunately I do not have the ability to alter the export format, and the end users of this msAccess DB need a "Magic button" approach.

My problem is that I cannot use "DoCmd.transferText" because it jumps to a new column every time it sees a comma, needless to say this will, at best result in mis-matched data. When I save the CSV in Excel first then import it, the import works flawlessly.

So my question is: how do I use VBA to convert this CSV to an XLS prior to import?

The full path to the CSV file is stored in a form field, from which an "Import" macro is launched. The form field is [imprt]![pth]

 

[SkipVought]

hi,

You will have the very same problem regardless of the method!

What LOGIC would you propose to avoid or fix the problem that you explained, without regard the the METHOD (code or import technique) of employing that logic?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[greyoxide]

If I manually convert the file to XLS then I can run an Import/Export spreadsheet function in my macro. I have tested this process and it works well.

The trouble is getting Excel to convert the file first. I have Googled this issue and I have found a few threads where people are passing actions on to Excel from a VBA module. The trouble is: I know nothing about passing commands on to Excel so I am unable to adapt their examples to fit mine.

Where it me, I would try to folow a path somewhat like this:

varPath = [Forms]![imprt]![Pth]

open Excel

Pipe in file varPath
save file as (varPath & ".XLS")

Close Excell

DoCmd.importSpreadsheet (arguments)

kill (varPath & ".XLS")

My big problem is that I have no idea how to pipe commands from access to excel.

 

[PHV]

Use OLE Automation.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

What does "If I manually convert the file to XLS " mean?

What method did you use to perform this?

I know of at least TWO methods, only one of which I would recommend.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[greyoxide]

I Right-mouse click on the CSV file and select open with "Excel". Or as an alternative I can select import text file in excel. In both cases I have hit save as and selected xls.

This worked well with the importer.

 

[SkipVought]

The latter is preferred as you can exert control over the parsing.

But I fail to understand how the THOUSANDS COMMAS are handled any better in Excel than in Access?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vbajock]

The key to parsing out one of these files is to realize that any comma contained between beginning and ending quote marks can be logically assumed to be data, and not separator, in other words you code has to see each quote " to " end quote as a separate data container. Aren't there any quote marks in this file of yours?