Convert comma delimited.txt or .csv file TO pipe delimted.txt file

I assume you mean comma (,) delimited rather than apostrophe (') delimited.

I'm assuming they all have the same structure. Import one of the files into a temporary table (call it tblTemp or something like that). On the last page of the Import wizard, click Advanced, and then save the spec as something like "MyImportSpec". Now export the records you've just imported. Choose the pipe character as the field delimiter. Again, on the last page, click Advanced, and save the spec as "MyExportSpec".

You can now use code like this to loop through the files:

Dim strFileName as String

strFileName = Dir("C:\MyFolder\*.csv")
While strFileName<>""
   DoCmd.TransferText acImportDelim, "MyImportSpec", "tblTemp", strFileName
   DoCmd.TransferText acExportDelim, "MyExportSpec", "tblTemp", Left$(strFileName, Len(strFileName)-4) & ".txt"
   DoCmd.SetWarnings False
   DoCmd.RunSQL "DELETE * FROM tblTemp"
   DoCmd.SetWarnings True
Wend

This will read the .csv files from C:\MyFolder and export them with a .txt extension to the same folder.

Nice solution shanesuebsahakarn.

As shane mentions, files would ordinarily need the same structure (identical number of fields and field names) for the solution to work. Can get around that criteria as follows.

1- Go File --> Get External Data --> Import
2- Select one of the files (any one will do) that you want to convert.
3- In the Import Text Wizard, click, "Advanced..."
4- Ensure that File Format is, "Delimited," Field Delimiter is a comma (,) and that the Text Qualifier (if appropriate) is correct.
5- Click, "Save As...," type, "myImportSpec," in the, "Specification Name," field, and click OK.
6- Change the Field Delimiter to a pipe (|).
7- Click, "Save As...," again, type, "myExportSpec," in the, "Specification Name," field, and click OK.
8- Click Cancel to exit the Specification window, and Cancel again to exit the Import Text Wizard.
9- Create and save the following query (ClearSpecColumns):

DELETE *
FROM MSysIMEXColumns
WHERE SpecID In (SELECT SpecID FROM MSysIMEXSpecs WHERE SpecName=[Enter Specification Name]);

10- Run ClearSpecColumns for the myImportSpec and myExportSpec Specifications.

Note: Running this query deletes the columns from the specified Specifications. Doing this makes the specs more flexible for code use, but renders them unusable for manual import/export operations.


11- Modify shane's code to read as follows:

Dim db
Dim strPath as String
Dim strFileName as String, strNewFileName as String

set db = CurrentDB

strPath = "C:\MyFolder\"
strFileName = Dir(strPath & "*.csv")
While Not strFileName = Empty
  strNewFileName = strPath & Left$(strFileName, Len(strFileName)-4) & ".txt"
  DoCmd.TransferText acImportDelim, "MyImportSpec", "tblTemp", strFileName
  DoCmd.TransferText acExportDelim, "MyExportSpec", "tblTemp", strNewFileName
  db.tabledefs.delete "tblTemp"
  strFileName = Dir
Wend

An alternative might also be to read the file into a text buffer and run a replace function to replace commas with pipe characters. This can be reasonably efficient depending on file sizes and has the advantage of not causing database bloat, but whether or not this is an option depends on the size of your files. Reading/writing should be quite quick if you read the files in chunks of say, 32k and shouldn't result in a bottleneck.

folks,

i had the MS Access soloution idea as well. My manager did not like it because she fears loosing data on the import and export.

Allow me to define what she really would like to see possible (though I dont think it is).

She wants me to be able to take a long list of excel files

C:\before\111.xls           C:\after\111.csv
C:\before\222.xls           C:\after\222.csv
C:\before\333.xls           C:\after\333.csv

and convert them to pipe delimited with out ever having to open each individual excel file. In other words a "blanketed soloution". We would like to be able to run this macro or stand alone program and when we are done we have an idential list of files in a different directory with the same file names and a .csv extension. (and the files need to be pipe delimited.)

I could live with going into the control panel and manually changing the regional setting from a , to a | if someone can show me how to create and run a mass or blanketed conversion of all these xls files.

I can throw out more points if I need to do so.

Thanks so far.

Look forward to hearing back

Lee

mcallarse

I like your concept or the colomn and row count report. let me run it past mgmt. and Ill see about your points.

thx

lee

mcallarse

I like your concept or the colomn and row count report. let me run it past mgmt. and Ill see about your points.

thx

lee