c# - Import custom text format without separators -
i import .txt file format sql server table or convert each block of text pipe separated line.
which tools or c# solution suggests resolve issue?
any suggestions appreciated.
thank you.
================= input (.txt file) ================= id: 37 name: josephy murphy email: jmurphy@email.com description: bla, bla, bla, bla... id: 38 name: paul newman email: pnewman@email.com description: bla, bla, bla, bla... : : ========================= output (sql server table) ========================= id | name | email | description 37 | josephy murphy | jmurphy@email.com | bla, bla, bla, bla... 38 | paul newman | pnewman@email.com | bla, bla, bla, bla... : :
instead of datatable i'm writing directly sql server. need enter connection string , name of sql table in insert sql.
if adding many lines consider using sqlcmd.exe comes sql server. accepts separator data , string sql. never used insert, use select sql. there host of different command line executable work sql server. see webpage below https://msdn.microsoft.com/en-us/library/ms162816.aspx
using system; using system.collections.generic; using system.linq; using system.text; using system.data; using system.io; using system.data.sqlclient; namespace consoleapplication1 { class program { const string filename = @"c:\temp\test.txt"; enum states { find_output, get_seperator, get_table_header, get_data_table, end } static void main(string[] args) { states state = states.find_output; streamreader reader = new streamreader(filename); string inputline = ""; string connstr = "enter connection string here"; sqlconnection conn = new sqlconnection(connstr); conn.open(); sqlcommand cmd = new sqlcommand(); cmd.connection = conn; string[] headers = null; while ((inputline = reader.readline()) != null) { inputline = inputline.trim(); if (inputline.length > 0) { switch (state) { case states.find_output: if (inputline.startswith("output (sql server table)")) state = states.get_seperator; break; case states.get_seperator: state = states.get_table_header; break; case states.get_table_header: headers = inputline.split(new char[] { '|'}, stringsplitoptions.removeemptyentries); headers = headers.select(x => x.trim()).toarray(); state = states.get_data_table; break; case states.get_data_table: string[] dataarray = inputline.split(new char[] { '|'}, stringsplitoptions.removeemptyentries); dataarray = dataarray.select(x => x.trim()).toarray(); string commandtext = string.format("insert table1 ({0}) values ({1})", string.join(",", headers), "'" + string.join("','", dataarray) + "'"); cmd.commandtext = commandtext; cmd.executenonquery(); break; } } else { if (state == states.get_data_table) break; //exit while loop if blank row @ end of data table } } reader.close(); conn.close(); } } }
Comments
Post a Comment