11Feb

Import CSV or Tab Delimited Data with C#, ASP.NET MVC

Filed in ASP.NET MVC | C# 3 Comments

In this sample we are going to take a different approach to importing data from the traditional file upload method. This sample has a textarea and a button. Users will open their .csv or .txt file, copy the contents, paste them into the textarea, and submit.

Setting up the form

This part is pretty basic:

<textarea id="bulkcsvlist" name="bulkcsvlist" rows="20" cols="105"></textarea>
<input type="submit" value="Upload File" />

Create import method

This sample shows the basics of parsing and adding the records, which assumes is uploading into a Contact table, with the first two fields in the row being FirstName and LastName respectively. You probably want to bake in your security checks and data validation. 

[AcceptVerbs(HttpVerbs.Post)]
public ActionResult UploadAll(FormCollection f)
{
    string bulkcsvlist = f["bulkcsvlist"].ToString();

    //
    //    Split the rows into an array
    //
    string[] rows = bulkcsvlist.Split(new char[] { '\n', '\r' }, StringSplitOptions.RemoveEmptyEntries);

    for (int i = 0; i < rows.Length; i++)
    {
        //
        //    Split the tab separated fields (comma separated split commented)
        //
        //string[] dr = rows[i].Split(new  char[] {','});
        string[] dr = rows[i].Split(new char[] { '\t' });

        //
        //    Add Contact for current row
        //
        if (dr.Length > 0)
        {
                Contact c = new Contact();
                c.FirstName = dr[0].ToString();
                c.LastName = dr[1].ToString();
                c.CreateAndFlush();
                c = null;
        }
    }
}

Normally I’ll throw each successful add into a List<> so whoever is uploading can validate the records look right or do any post-upload polishing, and the errors into a ViewData to display so the user can fix whatever needs fixing.

If you want a working sample, let me know!

, ,

22Jan

Import CSV file to MySQL

Filed in MySQL 1 Comment

Importing data into one of your MySQL tables is really easy using the Load Data command. In this exercise we will look at importing a comma separated list of countries into our Country table. Below is the script you would run in your MySQL Query Browser (or phpMyAdmin tool):

LOAD DATA LOCAL INFILE '<span style="color: #0000ff;">c:/samples/countrylist.csv</span>'
INTO TABLE <span style="color: #0000ff;">Country
<span style="color: #000000;">IGNORE 1 LINES</span></span><code></code>
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(<span style="color: #0000ff;">Value,    Description,    ActiveFlag,    SequenceID</span>);

Now we’ll break down a couple of the lines above in more detail.

LOAD DATA LOCAL INFILE

The Load Data Infile statement loads in rows from your text file into the table specified in the next line (INTO TABLE). Above you’ll notice I inlcuded the LOCAL option. This means the file will be read locally from your PC, and you have two additional options for identifying the location:

  1. Full Path – include the entire path including the file name, but instead of using a backslash, use a forward slash (like above).
  2. Relative Path – just include the file name. Note: make sure the .csv file is in the same directory as your MySQL Query Browser.

If you do not include the LOCAL option, then the .csv file must be on the server. Like the LOCAL option, if you include the full path, it is pretty simple, the specified path must exist on the server. If you use the relative path, the plot thickens. There are two ways to identify the location:

  1. ./countrylist.csv – the leading ./ is treated as a reference to the server’s data directory.
  2. countrylist.csv – is read from the default database’s directory. What that means is if your default database is called PrimaryDatabase, and you want to upload the countrylist.csv file into a database called CountryDatabase (yeah, I know the sample names are lame!), you would have to modify the INTO statement like this:

    INTO TABLE CountryDatabase.Country

IGNORE 1 LINES

This statement is typically used when you have a header record, if you have no header, you can leave it out.

FIELDS TERMINATED BY

This is the character that delimits each field. In the code snippet above, I’m using a comma. If you had a tab-delimited file, you would use ‘\t’ instead of the comma.

LINES TERMINATED BY

The TERMINATED BY portion is one of two options, and happens to be the option I always happen to take. This specifies the row delimiter. By default, it is the standard \n, although you could use \r\n. If you don’t specify TERMINATED BY, you and specify STARTING BY. What this does is allows you to prefix any row with a string of characters. For example, you could have the following statement:

LINES STARTED BY ‘pre_’

And then three rows of data that looks like this:

pre_”Joe”,”Smith”,1
pre_”Keith”,”Booth”,2
“Gary”,”Williams”,3

Only the first two rows would import because they have the pre_ STARTED BY prefix.

The last line is the list of fields . If you leave out that line, your file is expected to have a field for each column in the target table. If you are only importing a subset of the columns, then you need to specify the columns so the importer can properly map the data.

, , , , , ,

TOP