Best method to attach or import data from a comma delimited file to Access.

Question

User-8153698 posted

Hi,

 

I have a project I am working that requires me to go to an FTP port and copy a file to the local machine... so far so good... the next step is to take the comma delimited file and import the records (rows) into a local Access database...

Can anyone suggest the best method to achieve this?

Should I be looking the Attach method in Access?  To attach and dump all the data in one shot?

OR

To open the file with StremRead and process each row one at a time? 

I am using VS2008, C#, .NET 3.5, Access 2007... once in production there could 20 - 30 thousand records to be appended to the DB then normalised.

Thanks

 

Answer 1

User-821857111 posted

Like this: http://www.mikesdotnetting.com/Article/67/Reading-Text-files-into-Access-with-ASP.NET

Answer 2

User-8153698 posted

Thanks Great article and very clearly put down...

Thanks 

Answer 3

User-8153698 posted

Hi Mike,

Well I thougt I had thsi one down after reading the document you pointed to... EXCEPT it doesnt work for me... I keep finding that the Connection (DBConn) is closed!!!!... I've been staring thsi one in the face since yesterday morning wiht no solution in sight yet... here is my code ...

Query Builder...

                string query = "INSERT INTO ";
                if (DBTableName == "RAW_Transaction_Data")
                {
                    query = query + DBTableName + " ( post_tran_cust_id, message_type, tran_type, extended_tran_type, datetime_tran_gmt, recon_business_date, source_node_name, settle_amount_rsp, to_account_id, pan, agent_id, sub_agent_id, terminal_id, card_program, merchant_type ) SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14, F15 FROM [Text;HDR=NO;DATABASE=" + HttpContext.Current.Request.MapPath("~") + "FTP_DL\\" + ";].[" + FileName + "]";
                }
                else if (DBTableName == "RAW_Customer_Data")
                {
                    query = query + DBTableName + " ( first_name, middle_name, last_name, date_of_birth, address_1, address_2, city, region, code, country, email_address, phone_type, phone_number, id_type, id_number, pan, expire_date, customer_id, card_program, card_status, activation_date, hold_response_code, last_update_date ) SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14, F15, F16, F17, F18, F19, F20, F21, F22, F23 FROM [Text;HDR=NO;DATABASE=" + HttpContext.Current.Request.MapPath("~") + "FTP_DL\\" + ";].[" + FileName + "]";
                }

                int rowsAffected = myDC.NonQueryDataIns(query, 2);
                return rowsAffected;

-------------------------------------------------------------------------------------------------
Now the actual execution bit...

                string insSQL = pSQLString;
                CreateConnection();
                OleDbCommand cmd = new OleDbCommand();
                OleDbTransaction trans;

                // set the properties of the cmd Object
                if (pcmdType == 1)
                    cmd.CommandType = CommandType.StoredProcedure;
                else if (pcmdType == 2)
                    cmd.CommandType = CommandType.Text;
                else if (pcmdType == 3)
                    cmd.CommandType = CommandType.TableDirect;

                cmd.CommandText = insSQL;
                cmd.Connection = dbConn;
                trans = dbConn.BeginTransaction();
                cmd.Transaction = trans;

                // add new parameter to command object & Execute it
                int rowsAffected = cmd.ExecuteNonQuery();
                cmd.Transaction.Commit();
                dbConn.Close();
                return rowsAffected;

 

 

Answer 4

User-821857111 posted

I keep finding that the Connection (DBConn) is closed!

That's because you never open it.

Add dbConn.Open(); to line 18.

Answer 5

User-1199946673 posted

Add dbConn.Open(); to line 18.

 

I think you need to open the connection before calling the begintransaction method, which requires an open connection, so it should be added before line 16...

Answer 6

User-821857111 posted

Good spot.

Answer 7

User-8153698 posted

Sorry my bad... I didnt include the code behind CreateConnection() 

               // Make the connection to the DB
                string a = varAppTitle + ".Properties.Settings." + varAppTitle + "ConnectionString";
                dbConn = new OleDbConnection(Settings.GetConnectionStringByName(a));
                dbConn.Open();

                // write to the Log File
                ExceptionLog("CreateConnection Success - " + a);
                return dbConn;

What I have found is that the trans = DBConn.BeginTransaction line causes the DBConn to get closed.  If I remove that and the associated OldDBTransaction trans; cmd.Transaction = trans; and cmd.Transaction.Commit();  The code segment runs every time.

The code with the Transaction lines in would give all sorts of strange errors from Memory usage, to Connection closed, to well too many to remember this early in the morning.

I included the Transaction lines because when I built this class before transactions would not stick without them... this has got me stumped!  Do I have the lines in the wrong places?

Answer 8

User-8153698 posted

Well I have 2 different files that use the same function to insert the data into the DB. I have checked and rewritten it more times than I care to think about. I am now back to the original error that the Connection is getting closed. I have changed ALL the columns in both tables to type=Text in the DB so there should be no issues with "type". This time I ran it and got {"Syntax error in INSERT INTO statement."}... I have changed nothing! I check it immidialy before the Execute and it is open. NOw I ahve a different error... {"You must enter a value in the 'RAW_Transaction_Data.post_tran_cust_id' field."} I checked the file and there are now empty values for this (1st) column 

Answer 9

User-8153698 posted

I had all but given up on this and was ready to tell the PM to get someone else to finish the project.

Now it works... not sure exactly what I did or didn't do... So I am unable to tell anyone else who has such a nighmare what to do with the code to make it work!!!.

Sorry about that people