Load csv file with text qualifier as general text
maru
I want to load a csv file and auto-skip the header and column names row.
LoadWave/G was supposed to do this work, but my csv files contain the qualifier "", preventing /G to detect the data block.
I know /J and /L can be used for file loading but the number of header lines and column names may change per file, making it challenging to find the first line of the data block.
If I delete all "" from the csv file, I could load the data with LoadWave/G. I believe the problem happens in the text qualifier.
Is there a technique I can load csv file with text qualifiers as general text?
Function LoadCSV()
Variable refNum
String message = "Select one or more files"
String outputPaths
String fileFilters = "Data Files (*.csv,*.txt):.csv,.txt;"
Open /D /R /MULT=1 /F=fileFilters /M=message refNum
outputPaths = S_fileName
if (strlen(outputPaths) == 0)
Print "Cancelled"
Abort
Endif
Variable numFilesSelected = ItemsInList(outputPaths, "\r")
Variable i
for(i=0; i<numFilesSelected; i++)
string path = StringFromList(i, outputPaths, "\r")
LoadWave/Q/G/N=tempwave/D/O path
endfor
End
Variable refNum
String message = "Select one or more files"
String outputPaths
String fileFilters = "Data Files (*.csv,*.txt):.csv,.txt;"
Open /D /R /MULT=1 /F=fileFilters /M=message refNum
outputPaths = S_fileName
if (strlen(outputPaths) == 0)
Print "Cancelled"
Abort
Endif
Variable numFilesSelected = ItemsInList(outputPaths, "\r")
Variable i
for(i=0; i<numFilesSelected; i++)
string path = StringFromList(i, outputPaths, "\r")
LoadWave/Q/G/N=tempwave/D/O path
endfor
End
You can skip lines in the file by using the /L flag. If you know that your data always starts on the 18th line, just hardcode this in:
LoadWave/Q/J/N=tempwave/D/O/L={0, 17, 0, 0, 0} pathIf the header size varies you could scan through your file using FReadLine until you hit some indicator of your data block (for example the line after 'sec' appears in the file).
October 22, 2022 at 08:16 am - Permalink
Chozo's command works correctly in Igor Pro 8 and 9 which have improved handling of quoted text.
In Igor Pro 7 and before, it loads the data, including the quote marks, as text waves.
October 24, 2022 at 04:32 am - Permalink
Thank you for the comments. I am using Igor 9, so /J/L={...} works for my CSV file. However, as I mentioned that the starting line of the data changes per file, and the column headers of the data also change. /G works perfectly for these files if there is no qualifier.
Maybe I should programmatically delete all " from the CSV file and load it again with /G flag.
October 24, 2022 at 06:22 pm - Permalink
I recommend that you write a routine that finds the first line of data by searching for something you expect in the file such as "sec","N","mm" if that is reliably the first line in the file.
For an example of programmatically finding the first data line in the file, see https://www.wavemetrics.com/code-snippet/finding-first-line-data-plain-…
Another approach is to strip the double-quote characters from the file and then use Load General Text which will automatically find the first line of data. For an example, see https://www.wavemetrics.com/code-snippet/create-cleaned-version-plain-t…
If you need help, let me know.
October 25, 2022 at 08:06 am - Permalink
If your column headers change as well, then you can surely find other entries which indicate the start of the data. It seems entries like "1_1" are a good start (this reads to me as it is the first data block of the first measurement and I guess you can have multiple entries in one file). As Howard mentioned, the code to get this to work is not too complicated.
October 25, 2022 at 08:20 am - Permalink
Thank you so much for the helpful advice. As the header info change irregularly, including the 1_1, striping the double-quote characters from the file seems the best option. Thank you again for the help.
October 25, 2022 at 04:03 pm - Permalink