Parsing CSV into database and back

Question

I’m in the planning stage of a project currently and we have to make our software work with other client systems.

One of these systems is for managing orders within their business, and we need the information regarding stock. Sadly, they have no API, and users can only manually export a CSV file.

I can’t come up with the best solution on how to get this CSV data in to our app. At the moment, all I can think of is a manual CSV upload feature that we can use to parse the data in to our database. The main issue is that any changes we make to the data has to be put back into their system, which currently is through CSV upload also. We are supplied no reference data or anything that will help us map the data accurately. All of this on top of the fact that it has to work across multiple devices, probably simultaneously.

Can anyone give me an idea of what route to take to solve this problem, and if necessary, should I try to extend the project and recommend that they develop an API?

Thanks a lot in advance.

Answer 1

There are so many variables here so I can only give you some generic advice.

It depends on how current the information in the clients system needs to be. if the client wants orders made in the app to appear in their systems almost instantly its probably better to build an API, but then API could be difficult depending on what the client is using.

If you want to keep the cost low and use CSV, you would have to export any data changes made in your app to a CSV file and then transfer the CSV file to somewhere the clients system can grab it and import it. This would best be done on set time intervals, depending on how frequent the orders need to come through (could be every 5 mins or even every 24 hours).

The import process could be automated by system scripts like shell script. also you would need to set up an export from the clients system that your app would then use to update its own information this could also be managed by system scripts.

Use system scripts to import/export the physical file and then use your application to move the data between systems.

If you choose to do CSV import/export the mapping is essential, you need to investigate what the client system requires for a successful import and you need to make your application output match those requirements.

Answer 2

You must have some consistent schema you and the source share, otherwise you maze well parse free text.

DataTable csvTableSchema = new DataTable();

//Open the CSV
string csvFilePath = "C:\\temp\\A.csv";
var connString = string.Format(
    @"Provider=Microsoft.Jet.OleDb.4.0; Data Source={0};Extended Properties=""Text;HDR=YES;FMT=Delimited""",
    Path.GetDirectoryName(csvFilePath)
);

//To read the csv with DataTypes we specify the columns and their datatypes in the Schema.ini
//REF https://learn.microsoft.com/en-us/sql/odbc/microsoft/schema-ini-file-text-file-driver

using (var conn = new OleDbConnection(connString))
{
    conn.Open();
    var query = "SELECT * FROM [" + Path.GetFileName(csvFilePath) + "]";
    using (var adapter = new OleDbDataAdapter(query, conn))
    {
        var ds = new DataSet("CSV File");
        adapter.Fill(ds);
        csvTableSchema = ds.Tables[0];
    }
}

The Schema.ini file lives in the same directory as the CSV you're reading, you need to specify this for the reference data to allow you to map the data accurately eg:

[ABC_20171128.csv]
Format = Delimited(|)
CharacterSet=ANSI
ColNameHeader=True
DateTimeFormat=dd MM yyyy hh:nn:ss

Col1 = Date DateTime
Col2 = AccountID Text
Col3 = SubAccount Text
Col4 = MarketCode Text
Col5 = SecurityCode Text
Col6 = Units Single

Ref: Missing column of Dates in CSV file using Schema.ini file

Answer 3

There are so many ways to go with this, but only because you didn't provide enough information...in my opinion... Anyway, how about something like this, to get started.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
using Excel = Microsoft.Office.Interop.Excel; 

namespace Import_CSV_file_into_DataGridView
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            OpenFileDialog dlg = new OpenFileDialog();
            DialogResult dlgResult = dlg.ShowDialog();
            if (dlgResult == DialogResult.OK)
            {
                txtPath.Text = dlg.FileName;
            }
        }

        private void btnLoadData_Click(object sender, EventArgs e)
        {
            string rowValue;
            string[] cellValue;

            if (System.IO.File.Exists(txtPath.Text))
            {
                System.IO.StreamReader streamReader = new StreamReader(txtPath.Text);

                // Reading header
                rowValue = streamReader.ReadLine();
                cellValue = rowValue.Split(',');                
                for (int i = 0; i <= cellValue.Count() - 1; i++)
                {
                    DataGridViewTextBoxColumn column = new DataGridViewTextBoxColumn();
                    column.Name = cellValue[i];
                    column.HeaderText = cellValue[i];
                    dataGridView1.Columns.Add(column);
                }

                // Reading content
                while (streamReader.Peek() != -1)
                {
                    rowValue = streamReader.ReadLine();
                    cellValue = rowValue.Split(',');
                    dataGridView1.Rows.Add(cellValue);
                }

                streamReader.Close();
            }
            else
            {
                MessageBox.Show("No File is Selected");
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Excel.Application xlApp ;
            Excel.Workbook xlWorkBook ;
            Excel.Worksheet xlWorkSheet ;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            int i = 0;
            int j = 0; 

            for (i = 0; i <= dataGridView1.RowCount  - 1; i++)
            {
                for (j = 0; j <= dataGridView1.ColumnCount  - 1; j++)
                {
                    DataGridViewCell cell = dataGridView1[j, i];
                    xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
                }
            }

            xlWorkBook.SaveAs("C:\\Users\\Excel\\Desktop\\test.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

            MessageBox.Show("Excel file created , you can find the file C:\\Users\\Excel\\Desktop\\test.xls");
        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }
        }
}

There are many other things you can do as well. Post back if you have additional questions.