convert excel to pipe delimited text file

Comma is most likely to be used in a data than pipe, check and verify before using a Delimiter. Always check that data file and ensure that it does not contain the delimiter that you are going to use in your SQLLDR or other loader utility that your DBMS supports. You will run into the issue like I did and sometime you may not notice where the load is successful but the data loaded incorrectly. This solution #2 is what I found online when I wasn't convinced with my solution #1 and I realized there got to be a better way and I found Barry Stevens Solution and it works well.Įxcel documents are not the best file type for loading data therefore, I would always request file into a flat file which your loader loves if not I would always convert into pipe delimited file NOT comma separated value. It produces the same result as the above. Change the name and file extension if you want, by default stays as csv even though a different delimiter.

Change the ‘Save as type’ to ‘CSV (Comma delimited)(*.csv)’.

Open the Excel file you want to export to a pipe delimited file.Find the List separator and change it from a comma to your preferred delimiter such as a pipe (|).There are 5 columns and the data file has 5 data as well. You can run your loader against this flat file to load your data. The new file *.csv file is created which you can open using your favorite text editor like notepad++ to view it as comma separated file. If you don't, you just need to save your excel document file 'save as type: CVS(Comma Delimited)(*.csv). We will discuss on converting excel file into a file that is database load ready and the problem we may encounter the csv file.Ĭonverting Excel into CSV (Comma Separated Value): This is an easy process which anyone can do it with few clicks. Most database loads data from a flat file with fields separated by comma. The most frequently used file type in most organization today is Excel document.There are various issues with loading data from Excel as most database may not support the functionality directly where you may need to use some third party utility. Hey, it does not hurt to ask the requester back in a format that you need to make your load process easier but you may or may not get what you have requested. I WISH, the requester sends the data file in the format we want and then the load is done in few minutes.

Convert excel to pipe delimited text file