Read CSV values from a .txt file and write into a .csv file using Python or just Windows?

[TrickyWitcher]

Attached is an example of the data. Normally, you'd think just copying one of the arrays, and pasting it in the highlighted blocks of an Excel spreadsheet would work, but all the values get pasted into one block. Is there a way around this without writing a program to do it manually? To clarify, this happens when I put them in a row or a column.

 

ExampleData.txt

[keskparane]

Just open with Excel and choose the correct delimiters.

[TrickyWitcher]

4 minutes ago, keskparane said:

Just open with Excel and choose the correct delimiters.

How would I choose the correct delimiters?

[keskparane]

Well if its a csv then just make sure it's using commas. You should get a preview so you can see changes.

[mariushm]

There's an IMPORT feature in Excel ... I use LibreOffice but from memory, it's something like  .... open new excel file , go in data menu, get external data : from text .. then specify delimiters (tabs, commas etc) and it shows you a preview.   LibreOffice Calc will read the csv files directly and you can save to excel files if you don't want to bother.

[vorticalbox]

On 11/18/2018 at 2:21 PM, keskparane said:

Just open with Excel and choose the correct delimiters.

Did you look at the data? It's arrays of numbers, I'm not sure excel will handle that?

[keskparane]

6 hours ago, vorticalbox said:

Did you look at the data? It's arrays of numbers, I'm not sure excel will handle that?

Well I don't use excel but I can import it into open office calc with no problems. Just need to add [] to the delimiters then once done delete the first column which is empty space

[Franck]

On 11/18/2018 at 8:37 AM, TrickyWitcher said:

Attached is an example of the data. Normally, you'd think just copying one of the arrays, and pasting it in the highlighted blocks of an Excel spreadsheet would work, but all the values get pasted into one block. Is there a way around this without writing a program to do it manually? To clarify, this happens when I put them in a row or a column.

 

ExampleData.txt

Excel is not perfect for line delimiters. If you write your own code the simple way to parse this text so you understand what it's doing is using these steps

1 - Read all the text into a variable

2 - Remove all spaces, new lines and open brace [ characters

3 - Replace all closing brace ] with new line

4 - save to new file

[mariushm]

For that exampledata.txt file, it's pretty simple...

open notepad

edit > Replace > search for [  , replace with ""  (nothing) ... replace all

edit  > Replace > search for ]  , replace with ""  (nothing) ... replace all

file > save as  > save as type : all files > file name  : YOUR NAME.CSV

 

load your csv file in excel or libreoffice calc

[vorticalbox]

11 hours ago, mariushm said:

For that exampledata.txt file, it's pretty simple...

open notepad

edit > Replace > search for [  , replace with ""  (nothing) ... replace all

edit  > Replace > search for ]  , replace with ""  (nothing) ... replace all

file > save as  > save as type : all files > file name  : YOUR NAME.CSV

 

load your csv file in excel or libreoffice calc

True but it's a nice real world use case to get into python and using files and arrays.