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:
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)
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")
DoCmd.TransferText acImportDelim, "MyImportSpec", "tblTemp", strFileName
DoCmd.TransferText acExportDelim, "MyExportSpec", "tblTemp", Left$(strFileName, Len(strFileName)-4) & ".txt"
DoCmd.RunSQL "DELETE * FROM tblTemp"
This will read the .csv files from C:\MyFolder and export them with a .txt extension to the same folder.