topic Re: How to save CSV file with custom text before headers in Discussions

How to save CSV file with custom text before headers

miguello — Thu, 03 Aug 2023 19:04:56 GMT

There is a way to import a csv file where headers start not from the first line. You just specify the on what line the headers are.

But let's say after I did something with the data table and I want to export it back to the same format, how would I do that? Let's say I have the text, and it is just one line.

So that this CSV in Excel looks like this:

and in a Notepad it looks like this:

Is there a clean way to do that purely in JSL?

The way I'm currently trying to do this:

The opposite operation of reading CSV file I'm doing by reading CSV as a text file into ex, figuring out where my headers start (they can start at different places) and getting the line number as headersLocation, then doing:

		dtInt_ = Open(
			Char To Blob( ex ),
			Import Settings(
				End Of Line( CRLF, CR, LF ),
				End Of Field( Comma, CSV( 0 ) ),
				Strip Quotes( 1 ),
				Use Apostrophe as Quotation Mark( 0 ),
				Use Regional Settings( 0 ),
				Scan Whole File( 1 ),
				Treat empty columns as numeric( 0 ),
				CompressNumericColumns( 0 ),
				CompressCharacterColumns( 0 ),
				CompressAllowListCheck( 0 ),
				Labels( 1 ),
				Column Names Start( headersLocation ),
				Data Starts( headersLocation + 1 ),
				Lines To Read( "All" ),
				Year Rule( "20xx" )
			),
			Invisible
		);

So, my current plan is to get my processed data table into a BLOB somehow, and then just do the opposite Blob To Char() function and prepend some text to it and save it as a CSV file.

I'm stuck into getting data table to blob, so that I could get it to Char (which probably not the right way), or somehow get the data table to CSV format as a text (not save as a file).

Alternatively, I can save it to a CSV file, then read that file back as text and prepend a line and save it again (deleting temporary CSV file) - but it seems to be a very awkward way of doing this.

Thanks,

Re: How to save CSV file with custom text before headers

miguello — Thu, 03 Aug 2023 19:36:08 GMT

Ok, for now I solved it this way:

dt = Current Data Table();

filePath = "C:\TEMP\test.csv";
dt << Save(filePath);
dt_text = Load Text File(filePath);
out = "Custom Line \!n"||dt_text;
Save Text File(filePath, out, mode("Replace"));

Is there is cleaner way without saving file twice? (Let's say it's going to be for multiple files that are pretty big themselves - so extra save operation matters).

Re: How to save CSV file with custom text before headers

txnelson — Thu, 03 Aug 2023 20:32:27 GMT

You might want to look into reading the whole csv file into a text string using

Load Text File()

You can then parse the string variable for the information you want.

Save Text File()

can then be used to write the file back to your hard drive.

Re: How to save CSV file with custom text before headers

miguello — Thu, 03 Aug 2023 21:53:47 GMT

Jim,

That's exactly what I'm doing in my current solution - see above.

The problem is that I need to do some data manipulation as well - so I need to load it as a data table as well.

The problem is how to get the data table into text (in CSV format)... I could only come up with save as CSV and then load it again as text in order in insert the string. This is one Save\Load cycle more than I'd hope to have - it becomes expensive when I have multiple large files.

Re: How to save CSV file with custom text before headers

txnelson — Thu, 03 Aug 2023 22:03:19 GMT

I believe your specified approach will prove to be the most efficient.

Re: How to save CSV file with custom text before headers

TroyJones — Wed, 09 Aug 2023 06:17:05 GMT

Thank you, I also want to know it.