1

Most of our automatically generated files contain integers, decimals, date, time, and sometimes varchar. This is instrumentation data. Since we will be uploading those files directly to a SQL Server database, we were trying to decide what is the most efficient file format for automatic generation from our instrumentation.

What is the most efficient file format for uploading data to a SQL Server database? I thought it would be Text format but when I did an experiment with CSV, CSV tends to take less space and is also easier for users to analyze.

Data file size would vary from 50K to 250K. Only a few range in MBs.

I am working on creating an application that would upload data into the db. We use LabView for our instrumentation data, so that is where we want the standardized file format.

I am leaning towards CSV. I do not have much experience with XML, so I might consider it that if the advantages outweigh those of CSV by a lot. Readability is my concern only in terms of quality control. There might be cases where the user would want to change or fix some data before they upload. Speed is also important but with such small file sizes, I am not too worried about it.

2
  • 1
    You seem to have two different requirements - 'speed' and 'readability'. For speed, I think the native format is the most optimal, but it gets down to how your instrumentation CAN be generated - you can read more on native data type import/export here - msdn.microsoft.com/en-us/library/ms191232.aspx Commented Mar 20, 2017 at 12:36
  • 1
    Space used is determined by the data and the size of the columns that the data is imported into. You may be getting some leading or trailing spaces. If you are letting the wizard generate your table...don't.
    – Kevin3NF
    Commented Mar 20, 2017 at 12:52

1 Answer 1

1

Generally, CSVs are the smallest file size for non-binary (text) data. XML adds a great deal of xml codes that bloat the file size. So CSVs tend to import faster. But XML importers can handle exception cases, etc., with more complexity.

In my career, I've worked with instrument imports at four different kinds of laboratories. Where there was no direct LIMS (Laboratory Information Management System)-to-instrument interface system in place, we found that delimited text files are the fastest to write working import scripts around.

CSV is a comma-delimited text file. If your input data will include commas, then using a different delimiter may make more sense, like a pipe character for example: | A CSV file or pipe-delimited file generally follows the rule that each row/line of text from column 1 to the end-of-line carriage return represents one row of sample data in your database. Then within that row, each column of data in your database is separated from other columns by your delimiter character(s), which could be commas or pipes or anything you want to use.

Text files are great as long as the data is consistent over time.

2
  • 1
    I would also suggest you do the import from outside the database. Write an application that can run on a schedule, rather than a job inside the DB. That's generally easier to document and maintain over time. And presents less strain on the DB server. If you can, that is.
    – CaM
    Commented Mar 20, 2017 at 16:28
  • CSV is going to be easier, generally speaking, than XML if the instrument data is clean. if you need any sort of audit trails, have the app move your data from "in" folder to "archive" folder if it cleanly imports or "error" folder otherwise...
    – CaM
    Commented Mar 20, 2017 at 16:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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