Very big file size of .csv/.txt files to convert to Excel data

[Simone Fick]

I have data files that get extracted from an online system which is saved in .csv format.
The row separator is tab-delimited and the column separator is pipe-delimited (|).

Currently the .csv file sizes are extremely big (300 to 330MB). When I convert them in Excel it's still quite big (150-190MB).
There are roughly 33 million cells of data (roughly 950 000 rows and 35 columns).
The files contain no formulas, conditional formatting or anything except the converted data, I did check for used range size and there are no extra empty cells after the last row or column.
I do realize the amount of data could be the problem of the file size?

I cannot just open the .csv file in Excel because it reads it as a comma separator and I lose some data on certain rows because 1 of the columns' data has a comma in.
Methods I've tried that works:

• f I import the .csv file in Excel and set the delimiter to pipe.
• I've also opened the .csv file in Notepad, save it as .txt and then import, which works
• Also opened .csv file in Notepad, copy data and paste in clean Excel workbook, then run text-to-column and set delimiter as pipe
• I'm trying to use Power Query to see if this works easier and quicker as an option at the moment.

I will have to extract and convert these files to Excel files on a daily basis, which is time-consuming since the process is tedious but also the big files take long to open.

So my questions are:

• Is is normal for the file sizes to be this big?
• Is there an easier way to do this conversion on a daily basis?
• Will Power Query work if I load these files into a Data model (The aim is to use the data on a weekly/monthly basis to create 20 different graphs that show shows either weekly or monthly comparisons)

If there are any other options I am open to it. I can do some VBA coding if that is needed.

[CK76]

• Is is normal for the file sizes to be this big?
  For Log file. It can get quite large. I often deal with log for PBX system with conferencing bridge and these can be quite large.
• Is there an easier way to do this conversion on a daily basis?
  Depends... It will be easier for us to help you. If you have small file that's representative of your .csv (about 50 to 100 rows of data to test things). You can upload csv in zipped file.
• Will Power Query work if I load these files into a Data model (The aim is to use the data on a weekly/monthly basis to create 20 different graphs that show shows either weekly or monthly comparisons)
  Yes, data model can handle millions of rows of data quite comfortably, but it will depend on your Excel version. 2010 will have issue, as data can't be directly loaded from PQ to Data model.
  2013 or later has new engine and is more efficient, but isn't compatible with 2010 version of data model.

EDIT: Oh if you need file as xlsx format and with data in sheet, Data model is not the best method as it does not load data to sheet (though you can use PowerView/PowerPivot to analyse and report on data).

[Simone Fick]

Thank you for your reply CK76.
I've attached an example .txt file which I've edited as an example (I only changed/removed sensitive info and deleted rows to only have about 100).

I will be using Excel 2013 or higher with these reports.

The reason I thought of Power Query is because I have to link other data reports to these raw data files, for example, targets, baselines, etc, which are all in different Excel files. So I thought it might be easier to create a relationship between these tables in power pivot and then create graphs from that?

[CK76]

Then I would set it up like below.

1. Designate a folder where txt/csv file would be stored.

2. Using Get & Transform, query the folder.

3. If only the latest file is needed, add variable 'LatestFile' in Advanced editor and use that as filter condition on Source[Date modified] column.

4. Remove all but the [Content] column.

5. Expand the column using custom delimiter (pipe character).

6. Change column data type as needed and load to data model.

Sample M code: Change Filter.Files() as needed.

Please Login or Register  to view this content.

[Simone Fick]

Thank you CK76, I'll set it up and let you know. Appreciate the time and effort.