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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

743 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