-3

Which tools and programming libraries are you aware of that can help accelerate CSV manual correction process when column separator characters are encountered within data elements?

I'm currently not able to correct the generation of source CSV files, such as adding quote delimiters. Manual verification is always required to certify that corrections are properly made.

In my case I currently need to run a portable solution on Win32 (i.e. doesn't require administration permissions) and open source would be preferable. Any language is a possibility. Destination is to a SQL Server 2008 instance with very limited permissions (BULK INSERT, OPEN ROWSET and such are not allowed). DTS/SSIS are also not available, neither is Visual Studio. The current method is opening up Excel, filter non-blanks on the last column, correct those filtered out rows, and then export to SQL Server via bcp or MS Access.

Thanks.

3 Answers 3

1

We implemented a pretty simple C# command-line program that acted as a pre-parser before bulk inserting. It worked quite fast and you could create something with the free Visual Studio Express (or even with just the compiler, I imagine).

To be clear, you would only potentially need Visual Studio to CREATE the program (you can do it without Visual Studio, but you shouldn't need to). To run the .exe on the destination machines, the requirements are far lower - you just copy the .exe and it will use the .NET Framework that was already there. If you didn't do anything fancy and targeted a low-enough version of the framework you probably wouldn't need to add anything to the clients. Worst case would be a .NET Framework update (and that would only be if you targeted a newer version of the framework or have very antiquated clients running XP RTM or older).

8
  • In %WINDIR%\Microsoft.NET\Framework\<version> there are C# compilers (csc.exe). I'll look into it and see if there are any limitations on just using the compiler. In the meantime, VS Express is not portable. Commented May 22, 2012 at 15:05
  • You don't have to port VS Express - just the C# executable you write. Surely your .exe can run on all your Win32 destinations? Commented May 22, 2012 at 15:07
  • Aaron, I was referring to portable as in portable app, when the installer application doesn't write to registry. I don't have administration privileges on my workstation. Commented May 23, 2012 at 9:53
  • What does an .exe have to do with the registry? Commented May 23, 2012 at 11:47
  • Some application installers write to protected sections in registry. Anyway, I believe the VS Express installer is an .msi (Microsoft Installer) if I recall correctly from what I installed at home. Commented May 23, 2012 at 13:21
0

I had a similar issue recently and wrote a simple command line executable in C# that replaces strings of text in the file. In my case the commas in the text are always followed by a space but the column separator commas are not so by using xp_cmdshell to replace the comma space combination with a space the file is "cleaned" before calling bulk insert.

-2

No good answer to this very painful data engineering question. But it's been a few years and things may be moving forward.
I'm working on a AI/ML tool for that purpose: CSV Studio. It works both offline as a downloadable and as online app. It may be able to help, even if some features are still missing from the online version (if it doesn't work for you, we are looking to partner with tough CSV issues to pilot new features and use cases - so reach out).
You will also find some self-help/pre-parsing tools like csvkit which don't necessarily fix the CSV issues but streamline the import process and make it easier to fix the errors without interrupting your workflow for each error. I would also put Papa parse in that category - it is so convenient running in-browser. These tools are generally designed for main memory applications (Excel) and cannot handle very large files.
A friend of mine also has an online app CSV Explorer which is veru convenient and handles a few syntax edge cases.

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