Import tab delimited text file to sql server

This forum is only for questions or discussions about working with the mojoPortal source code in Visual Studio, obtaining the source code from the repository, developing custom features, etc. If your question is not along these lines this is not the right forum. Please try to post your question in the appropriate forum.

Please do not post questions about design, CSS, or skinning here. Use the Help With Skins Forum for those questions.

This forum is for discussing mojoPortal development

This forum is only for questions or discussions about working with the mojoPortal source code in Visual Studio, obtaining the source code from the repository, developing custom features, etc. If your question is not along these lines this is not the right forum. Please try to post your question in the appropriate forum.

You can monitor commits to the repository from this page. We also recommend developers to subscribe to email notifications in the developer forum as occasionally important things are announced.

Before posting questions here you might want to review the developer documentation.

Do not post questions about design, CSS, or skinning here. Use the Help With Skins Forum for those questions.
This thread is closed to new posts. You must sign in to post in the forums.
7/3/2018 2:14:06 PM
Gravatar
Total Posts 74

Import tab delimited text file to sql server

I am trying to create a module that will take a tab delimited text file and import into SQL table.  But I am getting this error with my code.  My google search is coming up empty.  I changed the SqlParameterHelper call and added the CommandType.StoredProcedure, but it didn't help.  Any ideas or another way to use a FileUpload to upload a text file without storing it on the server?  I guess I could do an insert call for each record.  There could be upwards of ~531 Lines and 11 columns (34 KB).

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Table-valued parameter 2 ("@UploadTable"), row 0, column 0: Data type 0xF3 (user-defined table type) has a non-zero length database name specified.  Database name is not allowed with a table-valued parameter, only schema name and type name are valid.

HTML:

<asp:FileUpload runat="server" ID="TimesheetFile" AllowMultiple="false" />
<asp:Button Text="Upload" runat="server" ID="TimesheetFileUpload" OnClick="TimesheetFileUpload_Click" />

C# Code

    protected void TimesheetFileUpload_Click(object sender, EventArgs e)
    {
        if (TimesheetFile.PostedFile.ContentType == "text/plain")
        {
            FaceScanAudit faceScan = new FaceScanAudit();
            faceScan.LoadFile(DateTime.Parse(TimesheetDate.Text), TimesheetFile.PostedFile.InputStream);
            ReloadWithFilters();
        }
        else
        {
            TimesheetFileLabel.Text = "Please make sure you pick a text/plain file.";
        }

    }

Business C#

        public void LoadFile(DateTime date, Stream FileInputStream)
        {
            DataTable FaceScanExport = new DataTable();
            FaceScanExport.Columns.Add(new DataColumn("EmployeeNumber"));
            FaceScanExport.Columns.Add(new DataColumn("EmployeeFirstName"));
            FaceScanExport.Columns.Add(new DataColumn("EmployeeLastName"));
            FaceScanExport.Columns.Add(new DataColumn("DateMonth"));
            FaceScanExport.Columns.Add(new DataColumn("DateDay"));
            FaceScanExport.Columns.Add(new DataColumn("DateYear"));
            FaceScanExport.Columns.Add(new DataColumn("RegularHours"));
            FaceScanExport.Columns.Add(new DataColumn("Overtime1Hours"));
            FaceScanExport.Columns.Add(new DataColumn("VacationHours"));
            FaceScanExport.Columns.Add(new DataColumn("HolidayHours"));
            FaceScanExport.Columns.Add(new DataColumn("OtherHours"));

            StreamReader reader = new StreamReader(FileInputStream);
            List<string> list = new List<string>();

            while (!reader.EndOfStream)
            {
                list.Add(reader.ReadLine());
            }

            string[] lines = list.ToArray();
            string[][] cells = new string[lines.Length][];

            for (int i = 0; i < cells.Length; i++)
                cells[i] = lines[i].Split('\t');

            for (int i = 0; i < cells.Length; i++)
            {
                FaceScanExport.Rows.Add(cells[0], cells[1], cells[2], cells[3], cells[4], cells[5], cells[6], cells[7], cells[8], cells[9], cells[10]);
            }

            DBFaceScanAudit.UploadExport(date, FaceScanExport);

        }

Database C#

        public static void UploadExport(DateTime date, DataTable data)
        {
            SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetReadConnectionString(false), "MANCON_SP_EXCEL_FaceScanAudit_TableImport", CommandType.StoredProcedure, 2);
            sph.DefineSqlParameter("@SelectedBatch", SqlDbType.Date, ParameterDirection.Input, date);
            sph.DefineSqlParameter("@UploadTable", SqlDbType.Structured, ParameterDirection.Input, data);
            sph.ExecuteNonQuery();
        }

Stored Procedure

ALTER PROCEDURE [dbo].[MANCON_SP_EXCEL_FaceScanAudit_TableImport]
    @SelectedBatch datetime = null
    ,@UploadTable PayClockTimeSheetTable READONLY
AS
BEGIN

-- - Insert From Table

    INSERT INTO MANCON_PayClockTime
    (EmployeeNumber, EmployeeFirstName, EmployeeLastName, Month, Day, Year, RegularHours, Overtime1Hours, VacationHours, HolidayHours, OtherHours)
    SELECT EmployeeNumber, EmployeeFirstName, EmployeeLastName, DateMonth, DateDay, DateYear, RegularHours, Overtime1Hours, VacationHours, HolidayHours, OtherHours
    FROM  @UploadTable

END

SQL User-Defined Table Type

/****** Object:  UserDefinedTableType [dbo].[PayClockTimeSheetTable]    Script Date: 7/3/2018 3:06:02 PM ******/
CREATE TYPE [dbo].[PayClockTimeSheetTable] AS TABLE(
    [EmployeeNumber] [nvarchar](20) NOT NULL,
    [EmployeeFirstName] [nvarchar](30) NOT NULL,
    [EmployeeLastName] [nvarchar](30) NOT NULL,
    [DateMonth] [int] NOT NULL,
    [DateDay] [int] NOT NULL,
    [DateYear] [int] NOT NULL,
    [RegularHours] [decimal](38, 20) NOT NULL,
    [Overtime1Hours] [decimal](38, 20) NOT NULL,
    [VacationHours] [decimal](38, 20) NOT NULL,
    [HolidayHours] [decimal](38, 20) NOT NULL,
    [OtherHours] [decimal](38, 20) NOT NULL,
    PRIMARY KEY CLUSTERED 
(
    [EmployeeNumber] ASC,
    [DateMonth] ASC,
    [DateDay] ASC,
    [DateYear] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO

You must sign in to post in the forums. This thread is closed to new posts.