How to automatically convert .txt file to .csv file format?

[fulham]

Hi,

I have a lot of .txt files that needed to be converted to .csv file format. Right now I am doing it manually using MS Excel i.e. File > Open. For each file, I need to specify the length of each fields one by one, so it is quite an effort for me.

Is there any way to automate this process using MS Excel or any other existing programs?

Thanks for your help!

 

[fulham]

Hi everyone.

Allow me to describe my situation here.

I am using SAP BW as my datawarehouse solution. Each month, I will be given 24 .txt files. However, my application, SAP BW, can only process .csv files. Thus, I need to manually convert these .txt files into .csv files. I do so by opening them in MS Excel File > Open and selecting let say A1.txt.

I will then be prompted with Text Import Wizard - Step 1 of 3 window, in which I use "Fixed width" to process my data. In the next window (Step 2 of 3), I need to carefully choose the length of characters for each column. Say if column A consists of 9 characters, column B 3 characters etc., I will have to specify it here in this window. Next in the third window (Step 3 of 3), I will also need to specify data format for each column. After finishing this process, I will then save my file to .csv file format.

So my question is, can we automate the processes using MS Excel or any other programs that are readily available on the Net?

Let me know if there is other question on this. Many thanks!

 

[DougStroud]

If you set up a query initially for these .txt files and specify the criteria, you can then perform this procedure repitively w/out doing anything more than selecting the refresh button for "all" in the query.

 

[fulham]

Hi Doug,

I am afraid I cannot relate your answer to my situation here. :D

Anyway thanks for replying to my topic!

 

[DougStroud]

How do you convert your data to csv from txt now?

 

[fulham]

Yes, Doug. I am converting my data to CSV from TXT at the moment using MS Excel.

Can u elaborate more on your previous answer coz I am not using any queries to do so.

Thanks dude!

 

[DougStroud]

Sure....
1) Open a new workbook/sheet
2) Top menu "Data", Import External Data, Import Data
3) Navigate to the directory where you store your files
4) Select the file
5) Begin the import, selecting the appropriate settings for your file
6) Set up a page in the workbook for each file you want to import. I have never tried importing data from different files to the same sheet, but assume that this would be similar.
7) Save the file as a csv.
8) When you update your files, you will overwrite them in the same directory. Then utilizing Excel's query feature, just select to update all from the menu, the one w/ the excel icon and the exclamation point.
This will import and overwrite your data each and everytime.
There are some settings you will need to set, but I can help you do this after you get the basic structure set.

 

[fulham]

Hi Doug,

Many thanks for your answer!

However, I need to clarify this with you. Say I have 24 files, that means I have to create 24 worksheets right? So every month, I will only need to run the update and they will all be overwritten to my 24 worksheets. Is that correct?

 

[DougStroud]

Yes, that would be my answer w/out testing (24 files, that is), but I do it w/ two files daily, just in the same manner as I described.

 

[fulham]

Hi Doug,

Thanks. Your insightful solution is very much appreciated.

However, as you can see from this scenario, I still need to open and format 24 .txt files (Using Text Import Wizard) into Excel before saving it to .csv format.

I wonder if there is any solution that enable me to do the formatting (aka Text Import Wizard) just once for all the 24 files?

Many thanks for your feedbacks.