How to convert Ods file to Data Set C

0 votes

I have downloaded the file from the following link UK List ods file link and used the following piece of code to convert this ods file to a data set.

int count = 0;
            int list_clear = 1;
            string connectionString;
            if (ConfigurationManager.AppSettings["OperatingSystem"] == "64Bits")
                connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
                "Data Source=" + location  + ";Extended Properties=\"Excel 12.0;\"";
            else
                connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                    location + @";Extended Properties=""Excel 8.0;HDR=YES;""";
    
    
               int recCount = 0;
                string xmlData = "";
                // Create a factory
                DbProviderFactory factory =
                       DbProviderFactories.GetFactory("System.Data.OleDb");
    
                // Create an adapter
                DbDataAdapter adapter = factory.CreateDataAdapter();
    
                // Create a select command to get the dataset
                DbCommand selectCommand = factory.CreateCommand();
                //selectCommand.CommandText = "SELECT * FROM [sanctionsconlist$]";
                selectCommand.CommandText = "SELECT * FROM [sheet1$]";// "SELECT * FROM [Data$]";
    
                // Set up the connection and the connection string
                DbConnection connection = factory.CreateConnection();
    
                connection.ConnectionString = connectionString;
                selectCommand.Connection = connection;
    
                // Set up the command
                adapter.SelectCommand = selectCommand;
    
                // Create the dataset
                DataSet ds = new DataSet();
                DataSet dsData = new DataSet();
                // Load the results
                adapter.Fill(ds);
                con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConStringName"].ConnectionString);
                cmd = new SqlCommand();
                con.Open();
                tran = con.BeginTransaction();
                cmd.CommandText = "USP_ImportUKListSanctionListData";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = con;
                cmd.Transaction = tran;
                cmd.Parameters.Add("@xmlData", SqlDbType.NVarChar, -1);
                cmd.Parameters.Add("@list_clear_flag", SqlDbType.Int);
                cmd.Parameters.Add("@update_list", SqlDbType.Int);
                cmd.Parameters.Add("@Error", SqlDbType.Int);
                DataTable dt = ds.Tables[0].Clone();

but i get the error, external table is not in the expected format

Can anyone help me in this regard?

Oct 7 in Others by Kithuzzz
• 20,660 points
71 views

1 answer to this question.

0 votes

You can see the answer here: Refer: stackoverflow.com/questions/1139390/…

answered Oct 8 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

How to convert data from txt files to Excel files using python

Hi , there are few steps to ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
6,215 views
0 votes
0 answers

How to convert a data frame column to numeric type?

How can we convert a data frame ...READ MORE

Jun 13 in Others by Avinash
• 1,260 points
51 views
0 votes
1 answer

How to update an existing excel .ods file?

It cannot be altered unless the original ...READ MORE

answered Oct 8 in Others by narikkadan
• 37,660 points
94 views
0 votes
1 answer
0 votes
0 answers

Error converting data type nvarchar to datetime SQL Server

I encountered this error: Error converting data type ...READ MORE

Aug 15 in Database by Kithuzzz
• 20,660 points
842 views
0 votes
1 answer
0 votes
2 answers

Is there a .NET equivalent to Apache Hadoop?

Hadoop is a Java-based platform. So, to ...READ MORE

answered Jul 16, 2020 in Big Data Hadoop by Suhana
• 340 points
776 views
0 votes
1 answer

Validate String against USPS State Abbreviations

Try something like this: private static String states ...READ MORE

answered Sep 20, 2018 in IoT (Internet of Things) by Annie97
• 2,160 points
278 views
0 votes
1 answer

How to convert PDF to Excel in C#?

Solutions a) Cometdocs makes a free online conversion from PDF ...READ MORE

answered Oct 1 in Others by narikkadan
• 37,660 points
95 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP