Link to home
Create AccountLog in
Avatar of leemurphy_asu1
leemurphy_asu1

asked on

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

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

Thanks

I look forward to a soloution!
Avatar of shanesuebsahakarn
shanesuebsahakarn
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of leemurphy_asu1
leemurphy_asu1

ASKER

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


ASKER CERTIFIED SOLUTION
Avatar of mcallarse
mcallarse
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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