C# script formatting CSV into SQL insert statement

Question

1.00/5 (1 vote)

See more:

Hello,

I'd like to take a .CSV file that is currently formatted as:

2018-08-16 06:06:40,35.938
2018-08-16 06:09:14,35.938
2018-08-16 06:24:14,34.862
2018-08-16 06:39:14,34.862
2018-08-16 06:54:14,35.399

and have an output like this:

("2018-08-16 06:06:40","35.938")
("2018-08-16 06:09:14","35.938")
("2018-08-16 06:24:14","34.862")
("2018-08-16 06:39:14","34.862")
("2018-08-16 06:54:14","35.399")

I would also like the CSV to move from directory C:\home\Script to C:\home\Script\processed

What I have tried:

I've search all over for any article relating to a topic such as this. I'm rather new to C#, so any suggestions will be a great help.

I appreciate your time,

Posted 17-Dec-18 5:35am

TheChewingTurkey

Updated 18-Dec-18 0:47am

Add a Solution

3 solutions

Solution 2

Based on the question title it sounds like you're trying to build INSERT statements with literal values based on the data in a CSV file.

If this is the situation, I would advice not to use the data from the CSV as is since this would leave you open to SQL injections. One very simple way to insert the data would be to use SqlBulkCopy Class (System.Data.SqlClient) | Microsoft Docs[^]

For an example reading CSV and inserting it into a table, have a look at c# - Import a CSV file to SQL Server using SqlBulkCopy - Stack Overflow[^]

Posted 17-Dec-18 5:52am

Wendelius

Comments

TheChewingTurkey 17-Dec-18 11:57am

Thanks for taking the time to make sure I wasn't putting myself in great security threat. However, this is more for a timed python script that I'm simply to lazy to format into a SQL doc.

Have a great day,

OriginalGriff 17-Dec-18 12:14pm

No, what he's saying is that if you use that text directly as part of an SQL statement, then you are at risk of SQL Injection: see what I've added to my answer.

Wendelius 17-Dec-18 12:34pm

I'm not sure if I understood you correctly but the point is, you should always use parameterized values when executing SQL statements. One scenario is SQL injection - Wikipedia[^] but not using parameters also easily leads to syntax errors, conversion problems with dates and numbers etc.

Solution 3

Hello,

OriginalGriff made me realize I was making things more complicated then I needed. What I ended up doing is fixing the python script generating the csv file:

Python

from gpiozero import CPUTemperature
cpu = CPUTemperature()
from time import sleep, strftime, time

with open("/home/pi/cpu_temp_4.csv", "a") as log:
    while True:
        temp = cpu.temperature
        log.write("(\'{0}\',\'{1}\'),\n".format(strftime("%Y-%m-%d %H:%M:%S"),str(temp)))
        sleep(900)

Now without any connection to SQL or any instance/database/table information, I receive this via script email from my Pi:

('2018-12-17 18:55:25','43.47'),
('2018-12-17 18:56:25','41.856'),
('2018-12-17 18:57:25','41.856'),
('2018-12-17 18:58:25','41.856'),
('2018-12-17 18:59:25','42.394'),
('2018-12-17 19:00:25','41.856'),

I take this file and copy into a SQL Query after I fill in the rest of the information needed, for easy data input.

I appreciate everyone's time and advice.

Posted 18-Dec-18 0:48am

TheChewingTurkey

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2018-12-17T05:48:00

Use string.Split to break each line of the input in two:

C#

string[] parts = input.Split(',');

Then use string.Format to "bolt them together" again:

C#

if (parts.Length == 2)
   {
   string output = string.Format("\"{0}\",\"{1}\"", parts[0], parts[1]);
   ...

Or even like this:

C#

if (parts.Length == 2)
   {
   string output = string.Format("(\"{0}\",\"{1}\")", parts[0], parts[1]);
   ...

[edit]
Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.
When you read data from your CSV and just process it to a "SQL Compatible" command, you put yourself at exactly the same risk.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?
[/edit]