MySql produced CSV file has all columns stacked in the first column

44 Views Asked by At

My MySql query is working fine and I am getting CSV output. The problem is the CSV file columns look like stacked in a single cell. I have total 24 columns. I confirmed it by opening in the Python and MS office.

MySql Query:

SELECT 'datetime','col2',...,'col24'
UNION ALL
SELECT 'DateTime','col2',...,'col24' 
INTO OUTFILE "SampleData.csv" 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '/n';

My present output when opened in the MS office:

You see all the 24 columns data stacked in the first column A here. enter image description here

My present output when opened in the Python:

The python also shows same response

df = pd.read_csv('SampleData.csv')
print(df.shape)
(0,1)

Expected output:

(1740,24)

UPDATE @FanoFN suggestions below, I found my mistake. Adding I made one more important and everything worked perfectly. My query:

SELECT 'datetime','col2',...,'col24'
UNION ALL
SELECT 'DateTime','col2',...,'col24' 
INTO OUTFILE "SampleData.csv" 
FIELDS TERMINATED BY ',' 
ENCLOSED BY "" # Here I deleted the ,
LINES TERMINATED BY '\n' # Here I corrected /n to \n
;

In Python: df = pd.read_csv(SampleData.csv) # no need to specify the delimiters Result:

enter image description here

0

There are 0 best solutions below