Convert Excel file into csv with Pipe symbol..

[mg24]

Hi Team,

I have excel file, I want to read that excel file and save it into csv files.
But delimiter I expect "|" symbol instead of comma,


1) In Data.xlsx workbook, ----, Select sheet named "Recon"
2) select range from "A10:Z10000" or till last row of data. ( Indirectly skip top rows.)
3) Create a csv file for only that range. with delimiter "|"
4) Output file name should be Recon.csv


Finally I want to insert those records into SQL Table. was my intention.

[Larz60+]

What have you written so far? Please share, working or not.

[deanhystad]

Do you need to generate the csv file? Sounds like that is an intermediate step that you can probably skip.

Look at https://docs.python.org/3/library/csv.html

It is obvious how you set the separator for csv readers and writers.

[Pedroski55]

Maybe you just really want to do this with Python, but it seems to me a very easy thing to do manually: Let PHP do the work!

The table you want to import into must of course exist and have the correct number of columns. (The same number as your XL, or vice-versa.)

1. Open your XL and save as csv. (If you really need a | as separator, can't see why, you can set that then.)
2. In phpMyAdmin, import the csv, entering the number of rows you want to skip under Partial Import

Conflicting IDs as Unique key will cause problems.

If you already have data in the table you don't want to repeat, click: "Update data when duplicate keys found on import (add ON DUPLICATE KEY UPDATE)"

Quick, easy, hassle free!
That way, you'll have more time to spend with the gf!

[Larz60+]

This is a simple task in python

• load the excel 'recon' sheet using pandas see: pandas.read_excel
  look at examples at bottom of page.
  
• write pandas frame: see DataFrame.to_csv
  look at attribute " sep='|' " (on same document page) to set your separator.
  

there are only 3 (4 if you make cell selection a separate line) lines of code necessary

1. import pandas
   
2. load the excel page
   
3. write selected cells to the csv page
   

That's it