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

Posted on 2003-02-28
Medium Priority
Last Modified: 2007-12-19
Alright here is the deal, I have almost had this a few times and cant seem to complete.

Using a macro (from within excel or access), I need to convert a long list of files like this:
C:\my docs\1.csv
C:\my docs\2.csv
C:\my docs\3.csv
etc. etc...

From comma (') delimited to pipe (|) delimited. When done running the code the files could have the same name or they could have the same name.txt and be in a differnt directory. (whatever)

Here is the catch, I need to figure out a way to accomplish this WITHOUT changing the regional settings in the control panel from using a (') to using a (|).

OR from in an excel doc that I am about to save can someone tell me how I can write a macro that would use the GetSaveAsFilename command and allow me to not only speify the save as type (which would be .csv) BUT also specify that I want to deliniate the file with |'s and not 's?  
(I could accomplish task this way because that long list of .csv files gets created this way. one save as at a time)

Winner gets 75 points


I look forward to a soloution!
Question by:leemurphy_asu1
  • 3
  • 2
  • 2
LVL 41

Expert Comment

ID: 8047431
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

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

Expert Comment

ID: 8048912
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):

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

LVL 41

Expert Comment

ID: 8049014
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.
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.


Author Comment

ID: 8059079

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



Accepted Solution

mcallarse earned 225 total points
ID: 8059257
You can't modify the contents of a file without...modifying the contents of the file; any method you find will need to open, save and close each file.

What data is your boss concerned about losing? For peace of mind, can get before and after field and row counts of each file, and throw that into a report (managers like reports). You will have an identical set of files, and can (I assume) create backups as necessary. Data loss should not be a concern here.

As mentioned above, you don't have to create custom specifications for each file, which will ensure that columns don't inadvertently get lost in the transfer.

Author Comment

ID: 8059498

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



Author Comment

ID: 8060439

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



Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Get the source code for a fully functional Access application shell with several popular security features that Access VBA application developers desire, but find difficult or impossible to figure out how to code. You get the source code for managi…
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question