Sometimes I have to load csv-files to database. These files might be really huge (> 1TB).

I did not export them to this file and I cannot change anything about the format. The files just "fly in". In most of the cases the files are seperated by "pipe". Sometimes text is qualified. Within qualified text there might be "pipes", as well as there might be CR/NL-characters... Sometimes text is not qualified. Within the text pipes are escaped with an backslash. A backslash might be escaped with another backslash...

The first thing I do to load these files is to get all content changed so that one line for the table is within on line of text. The row delimiter must not remain within any textfield. I do this job with a commandline script using sed and/or tr.

The next step is to split the file in hany pices (< 50 MB). They would fit into any editor (more confort than less) and I can proceed them parallel. (For a short time I need double the space of the original file in the filesystem).

Depending on the text formating I convert special characters within the text fields using commandline script using sed, if it gets more complex I use awk. As the original file is split to hundreds of small files I can do this parallel. To run (lets say 20) tasks parallel I use another commandline script.

The next step is to load these files into the database. For loading into MS-SQL-Server I use an SSIS-package with a for-each loop over all files within a filesystem directory. For each file a table is created and the data of this file is loaded. If successfull the file is moved to a subfolder "success", if an error occured the file is moved to a subfolder "error". So if something would stop the process, you could always restart at a defined point.

I build some copies of this package and filesystem-structures (load-folder_x, load-folder_x/success, load-folder_x/error) to start as many packages in parallel as my I/O would accept.

When every file is in "success" I am almost done: At least data is inside the database. So I can loop through all the tables and do whatever I can do within a database. All in all I need about 3 times the size of the original file for filesystem data and database file while the process is running. A single process from file in filesystem to table in database would run with about a 20 to 30 MB/s. It does scale up to the maximum I/O of the server/disk-subsystem/network-connection.

My question is: How to improve this process?

The used tools are all "handmade", not documented and not so easy to understand for third parties. errorhandling and logging is all "handmade" ... I guess there is a lot of potential improvement and I guess I am not alone with this kind of tasks.

  • 1
    So I'm not sure where you could improve other than write the documentation for it. Error handling is easy enough in SSIS if you build the framework around it, just get it logged to a table. – user507 Dec 16 '17 at 16:33

Certainly you are person who knows a lot of technology tools etc. But I will just point here, that two things are probably worth of research:

  1. Storage of the files on Linux filesystem. From my experience cames that even opening and listing directory with 100 000 or more files is a task which Microsoft cannot do in efficient way. Just using more efficient operating system here may give you at least some ergonomic gains.

  2. Maybe you should research etl tools like Apatar. It works with many files by GUI, so in some cases you may have a visualization of your process, which probably makes it easier to automate or analyse.

  3. It may be surprising in relation to this problem, but on the market there are very efficient data collection tools. Maybe thinking outside the box, and using clustered tools for system log acquisition, like Graylog log server, will be interesting choice. It has pipelining system, which takes data from network port, or through remote agents monitoring particular text files on remote system, and crunch it through various regex collectors, then has ability to divide that streams into output depending on given regex results. Output may be defined by you, and sent to another network device. Even on single machine acquisition of several hundreds of thousands records takes a seconds. It is very, very efficient. I believe at least part of your work may be automated here, for example it may test patterns and produce stream with wrong format entries ( or with good ones). And it gives you some basic statistical informations like dashboard with graphic representation of error types etc.

| improve this answer | |

Through SQL Server Import and Export Wizard You can create simple packages that import and export data between many popular data formats including databases,spreadsheets,and text files. The import and export wizard also create the destination database and the tables into which the data is inserted.

for you ref further follow the instruction here

| improve this answer | |
  • Import wizard will not handle text files that have various types of data and structure noted by the OP. – user507 Dec 16 '17 at 16:34
  • You are perfectly right: With SSIS Import and Export Wizard you can create simple packages. When things only get a bit more complicated this might not be the best way. – Christian4145 Dec 18 '17 at 11:37

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