1

Problem: I am trying to export a single column of a .CSV file that is 7 gigs in size (large enough that I can't open the entire file in excel... or most text editors for that matter) to a smaller file that I can then use or put into a database without all the unneeded information.

Power Query: I've made a "connection" to the file and then selected only the desired column, but I'm not sure if there's a way to export that.

Alternative?: Alternatively, using Powershell there's most likely a way from what I've seen based on this question, but I'm not sure where to begin with that.

If there's any other resources that would be better for this, anything helping point me in the right direction would be awesome.

Any help is greatly appreciated!

2
  • Have you seen this answer? It looks like it could process the file despite your file size concern with a pretty small amount of work. Aug 2 '18 at 1:29
  • Yes @Slartibartfast, that could have also been a possible solution. I may still need to use some parts of it since the (now successfully extracted) column that I needed is still 1,000,000+ rows. Thanks for your response!
    – OtterJesus
    Aug 2 '18 at 17:09
0

Using PowerShell method

Import-csv -Path $LargeFileName | Select-Object -Property $ColumnName | Export-Csv -Path $OutFileName -NoTypeInformation

Adjust the variables LargeFileName.csv, Header ColumnName and OutFileName.csv to your requirements

This following PowerShell method using gc and ConvertFrom-Csv to provide a faster result.

$CSV = Get-Content $LargeFileName | ConvertFrom-Csv | Select $ColumnHeading | Export-Csv -Path $OutFileName -NoTypeInformation

To get more headings use comma separation

$CSV = Get-Content $LargeFileName | ConvertFrom-Csv | Select $ColumnHeading1, $ColumnHeading2  | Export-Csv -Path $OutFileName -NoTypeInformation
2
  • Took a while to run due to size of the file, but it seemed to work. Many thanks!
    – OtterJesus
    Aug 2 '18 at 17:05
  • As a follow up, how would I do select multiple columns in the same file? Select $heading1 $heading 2? or something else?
    – OtterJesus
    Aug 2 '18 at 19:42
1

I'd suggest you check out TextQ (disclaimer - I'm its developer). It can import big CSV files and allows you to manage its schema/structure (e.g. rename or hide columns)

You can perform queries via a UI Query Builder or SQL.

You can then export the data to a CSV file, which can be imported in MS Excel or others.

You can get it from the Mac App Store or Microsoft Store (soon).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.