DataManager: Importing Data without columns headers

November 10, 2015 11:12AM

Hi

We have a project where we are sent text files of raw data with no header row and we get a separate file which has the column headers in. I need to append the column headers to the top of the raw data so I can use it. I don't want to put a field organiser and manually go through and add the column names The assumption is that the order in the headers file is in the same order as the data.

Has anybody done this before and how did you get over it? I have attached a method I'm looking at but ideally I don't want to have to write out a file and read it back in again (especially as the files are in excess of 2million rows).

Thanks

Andy

steve · November 11, 2015 1:28AM

Andy, have you tried concatenating the files using command prompt, something like this?

cat headers.csv > output.csv
cat data.csv >> output.csv

daniel · November 12, 2015 11:51AM

If you want to avoid using the cmd prompt, just append the two files together (in image or use batch append just need to make sure the first file is the header - it is treated alphabetically) in Omniscope (with "Has header" unticked) and output the new csv without a header.

Then read in the output csv and set the header accordingly.

Another way but slightly more complicated is to use R script to append files together. Using R script you can append multiple csv files within a directory space and might be more useful if you intend to update the data set over time and just saving all the data files into the same place.

This blog has a sort of guide but just google R script for appending csv with no headers:
https://psychwire.wordpress.com/2011/06/03/merge-all-files-in-a-directory-using-r-into-a-single-dataframe/

Welcome!

Categories

Ideas Parade

Tagged