I have a large CSV file which has information in a single column.How can I replicate the 'text to columns' task in excel using python?

Question

My data has a single column with rows like these : "148865220000000006 IMPACTED_NON_SERVICE_AFFECTING MAJOR 2017-03-04 23:59:46.3190 2017-03-05 00:00:44.8550 SERVICE 110999085 SERVICE NDE -1830810236"

I want to separate this into multiple columns delimited by tab. This task can be done in Excel using the "Text to Columns" option but due to the large size (1.46 GB), I am unable to open it in Excel.

"I want to separate this into multiple columns delimited by space." <- And then what? Iterate over the columns? Save as csv? — timgeb, Apr 11 '17 at 08:31
and save as CSV after the modifications (splitting into multiple columns) — Shreyas, Apr 11 '17 at 08:35
Tried it. But getting a blank CSV file. Closed my IDE and tried again, still getting a blank csv file — Shreyas, Apr 11 '17 at 09:49

score 0 · Answer 1 · answered Apr 11 '17 at 08:33

0

If you want to do it quickly using the csv reader you should go to the above mentioned duplicate question. (Hint: use "delimiter")

In case you want to accomplish the task manually:

You can use the split() function in Python.

On your own example:

mystring = "148865220000000006 IMPACTED_NON_SERVICE_AFFECTING MAJOR 2017-03-04 23:59:46.3190 2017-03-05 00:00:44.8550 SERVICE 110999085 SERVICE NDE -1830810236"
mystring = mystring.split()
print(mystring)

This prints

 ['148865220000000006', 'IMPACTED_NON_SERVICE_AFFECTING', 'MAJOR', '2017-03-04', '23:59:46.3190', '2017-03-05', '00:00:44.8550', 'SERVICE', '110999085', 'SERVICE', 'NDE', '-1830810236']

Thus my string is now a list where the data have been split by space. You can use e.g split(",") to split comma separated values.

answered Apr 11 '17 at 08:33

SiGm4

284
1
2
10

how can I carry this out on all the rows of a CSV file ? – Shreyas Apr 11 '17 at 08:38
Let's say you read the csv file to a variable called `streamreader` with csv.reader. I think that `for row in streamreader:` will do the trick. – SiGm4 Apr 11 '17 at 08:40
The code in duplicate doesn't work. It returns a blank csv file. – Shreyas Apr 11 '17 at 09:53

I have a large CSV file which has information in a single column.How can I replicate the 'text to columns' task in excel using python?

1 Answers1