locked
Import plain text data from an api to a table RRS feed

  • Question

  • User-1950594959 posted

    Hi, I am trying to import data from an api into a SQL Server table. The below code works for a text file. How do I read plain text data and write it to a table.

    public class DataController : ImportController
        {
            private IList<string> errors = new List<string>();
    
            [HttpPost]
            [Route("data/import", Name = "DataImport")]
            public IHttpActionResult Post()
            {
                var content = parse(this.Request.Content);
                
                postdata(content.lines);
                if (errors.Count == 0)
                    return Ok("Inserted");
                else
                    return Ok(errors);
            }
    
            private Models.DataImportFormat parse(HttpContent content)
            {
                var stream = content.ReadAsStreamAsync().Result;
    
                var parser = new MultipartParser(stream);
    
                if (parser.Success)
                {
                    var newDataImportFormat = new Models.DataImportFormat
                    {
                        fileData = parser.FileContents,
                        contentType = parser.ContentType,
                        fileName = parser.Filename,
                        lines = Encoding.UTF8.GetString(parser.FileContents).Split(new string[] { Environment.NewLine, @"\r" }, StringSplitOptions.None).ToList()
                    };
    
                    return newDataImportFormat;
                }
    
                return new Models.DataImportFormat();
            }
    
           private void importdata(IList<string> lines)
    
            {
    
                string connectionString = @"Data Source=localhost;Initial Catalog=TEST_DB;Integrated Security=True";
    
    
    
                var Data = from line in lines
    
                            let data = line.Split(',')
    
                            select new filedata
    
                            {
    
                                ID = data[0],
                                Type = data[1],
                                Status = data[2],
                                Description = data[3]
    
                            };
    
                    
    
                using (SqlConnection sqldbConnection = new SqlConnection(connectionString))
    
                {
    
                            sqldbConnection.Open();                       
    
                                foreach (var i in Data)
    
                                {
    
                                    try
    
                                    {
    
                                        using (SqlCommand cmd = new SqlCommand("INSERT INTO [dbo].[testImport] ([ID], [Type], [Status], [Description]) VALUES (@ID, @Type, @Status, @Description)", sqldbConnection))
    
                                        {
    
                                            cmd.Parameters.AddWithValue("@ID", i.ID);                                      
                                            cmd.Parameters.AddWithValue("@Type", i.Type);
                                            cmd.Parameters.AddWithValue("@Status", i.Status);
                                            cmd.Parameters.AddWithValue("@Description", i.Description);
    
                                            cmd.ExecuteNonQuery();
    
                                        }
    
                                    }
    
                                    catch (Exception ex)
    
                                    {
    
                                        Console.WriteLine(ex.Message);
    
                                    }
    
    
    
                                }                     
    
                }
    
            }
    
    
        }

    Thank you in advance.

    Thursday, November 16, 2017 7:02 PM

Answers

  • User283571144 posted

    Hi dotnetenthusiast,

    Hi Brando ZWZ, Thanks very much for your response. I made changes to the parse() method accordingly and get the following error.

    Could you please tell me what you need store to the table from the text file? Just the plain text? 

    You want to convert this text file's content to lines and select its value.

    If this is your requirement, I suggest you could not use parse method firstly convert the file to DataImportFormat.

    You could directly use my codes to get the text file's content and convert it to lines and use importdata method to save the content to database.

    More details, you could refer to below codes:

        public class PostFileController : ApiController
        {
            public IList<string> lines { get; set; }
    
            [HttpPost]
            [Route("data/import")]
            public IHttpActionResult Post()
            {
                if (Request.Content.IsMimeMultipartContent())
                {
                    var rest = this.Request.Content.ReadAsMultipartAsync().Result;
                    foreach (var item in rest.Contents)
                    {
                        if (item.Headers.ContentType.MediaType == "text/plain")
                        {
                            var re = item.ReadAsStringAsync().Result;
                            lines = re.Split(new string[] { Environment.NewLine, @"\r" }, StringSplitOptions.None).ToList();
                            //postdata(lines);
                        }
                    }
                }
                return Ok("Inserted");
            }
     
    
        }

    Result:

    If this is also not your requirement, I suggest you could firstly post your text file content format and which value you want to save to the sql database table.

    Best Regards,

    Brando

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 20, 2017 5:36 AM

All replies

  • User283571144 posted

    Hi dotnetenthusiast,

    Hi, I am trying to import data from an api into a SQL Server table. The below code works for a text file. How do I read plain text data and write it to a table.

    According to your description, I suggest you could consider using ReadAsMultipartAsync method.

    This method will read the request as the httpcontent.

    Then you could use ReadAsStringAsync method to read the file's content data.

    More details, you could refer to below codes:

            [HttpPost]
            [Route("data/import")]
            public IHttpActionResult Post()
            {
                if (Request.Content.IsMimeMultipartContent())
                {
                    var rest = this.Request.Content.ReadAsMultipartAsync().Result;
                    foreach (var item in rest.Contents)
                    {
                        if (item.Headers.ContentType.MediaType == "text/plain")
                        {
                            var re = item.ReadAsStringAsync().Result;
                        }
                    }
                }
    
    
                return Ok("Inserted");
            }

    Result:

    Best Regards,

    Brando

    Friday, November 17, 2017 6:47 AM
  • User-1950594959 posted

    Hi Brando ZWZ, Thanks very much for your response. I made changes to the parse() method accordingly and get the following error.

    Error message: "System.ArgumentNullException {"Value cannot be null.\r\nParameter name: source"}"

            private Models.DataImportFormat parse(HttpContent content)
            {
                byte[] byteArray = Encoding.ASCII.GetBytes(content.ReadAsStringAsync().Result);
                MemoryStream stream = new MemoryStream(byteArray);
    
                var parser = new MultipartParser(stream);
    
                if (parser.Success)
                {
                    var newDataImportFormat = new Models.DataImportFormat
                    {
                        fileData = parser.FileContents,
                        contentType = parser.ContentType,
                        fileName = parser.Filename,
                        lines = Encoding.UTF8.GetString(parser.FileContents).Split(new string[] { Environment.NewLine, @"\r" }, StringSplitOptions.None).ToList()
                    };
    
                    return newDataImportFormat;
                }
    
                return new Models.DataImportFormat();
            }
    Friday, November 17, 2017 10:10 PM
  • User-932240137 posted

    Hello, 

    I have similiar requirement that  i have a excel file in tableA and i need to read that excel file and save it's data to tableA using Web Api  or MVC ? 

    Can you help me on this please ? 

    Thanks,

    Saturday, November 18, 2017 6:35 AM
  • User1120430333 posted

    https://stackoverflow.com/questions/13717088/reading-data-from-excel-in-to-json-object-in-c-sharp

    You're going to do something to what is in the link where you convert the excel to Json,  and you send the data form the WebAPI client program to the WebAPI service program.

     https://docs.microsoft.com/en-us/aspnet/web-api/overview/advanced/calling-a-web-api-from-a-net-client

    Saturday, November 18, 2017 2:08 PM
  • User283571144 posted

    Hi dotnetenthusiast,

    Hi Brando ZWZ, Thanks very much for your response. I made changes to the parse() method accordingly and get the following error.

    Could you please tell me what you need store to the table from the text file? Just the plain text? 

    You want to convert this text file's content to lines and select its value.

    If this is your requirement, I suggest you could not use parse method firstly convert the file to DataImportFormat.

    You could directly use my codes to get the text file's content and convert it to lines and use importdata method to save the content to database.

    More details, you could refer to below codes:

        public class PostFileController : ApiController
        {
            public IList<string> lines { get; set; }
    
            [HttpPost]
            [Route("data/import")]
            public IHttpActionResult Post()
            {
                if (Request.Content.IsMimeMultipartContent())
                {
                    var rest = this.Request.Content.ReadAsMultipartAsync().Result;
                    foreach (var item in rest.Contents)
                    {
                        if (item.Headers.ContentType.MediaType == "text/plain")
                        {
                            var re = item.ReadAsStringAsync().Result;
                            lines = re.Split(new string[] { Environment.NewLine, @"\r" }, StringSplitOptions.None).ToList();
                            //postdata(lines);
                        }
                    }
                }
                return Ok("Inserted");
            }
     
    
        }

    Result:

    If this is also not your requirement, I suggest you could firstly post your text file content format and which value you want to save to the sql database table.

    Best Regards,

    Brando

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 20, 2017 5:36 AM
  • User-1950594959 posted

    Hi Brando ZWZ

    This helped Thanks very much.

    Regards

    Monday, November 27, 2017 6:06 AM