Bulk inserting a csv in SQL using a formatfile with 1 entry for multiple columns

Question

I am trying to import data from csv which has 7 columns into a View with 8 columns.

Here is my FMT;

14.0
8
1       SQLCHAR             0       50      ","      1     PaymentUniqueNumber                        SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR             0       50      ","      2     RequestDate                                SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             0       200     ","      3     Amount                                     SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             0       200     ","      4     Currency                                   SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR             0       200     ","      5     TransactionType                            SQL_Latin1_General_CP1_CI_AS
5       SQLCHAR             0       1000    ","      6     TransactionStatus                          SQL_Latin1_General_CP1_CI_AS
6       SQLCHAR             0       1000    ","      7     ExtractStatus                              SQL_Latin1_General_CP1_CI_AS
7       SQLCHAR             0       2000    "\r\n"   8     ReversalStatus                             SQL_Latin1_General_CP1_CI_AS

Note that 3rd entry is repeated for 2 columns. Basically the Amount format is 10.00 USD and during insertion, I want it to be divided into Amount and Currency Column.

This is what I have tried so far. Here is my select query

SELECT
    RowSource.PaymentUniqueNumber,
    RowSource.RequestDate,
    LTRIM(RTRIM(SUBSTRING(RowSource.Amount,0,CHARINDEX(' ',RowSource.Amount,0)))) AS Amount,
    LTRIM(RTRIM(SUBSTRING(RowSource.Amount,CHARINDEX(' ',RowSource.Amount,0)+1,LEN(RowSource.Amount)))) As Currency,
    --RowSource.Amount,
    --RowSource.Currency,
    RowSource.TransactionType,
    RowSource.TransactionStatus,
    RowSource.ExtractStatus,
    RowSource.ReversalStatus
    FROM OPENROWSET
    (BULK 'C:\test.csv', 
    FORMATFILE = 'C:\test.fmt',
    CODEPAGE = 'RAW',
    FIRSTROW = 2,
    MAXERRORS = 0,
    ROWS_PER_BATCH = 0
) AS RowSource;

View:

CREATE VIEW [dbo].[ReportsVW]
AS
SELECT        PaymentUniqueNumber, RequestDate, Amount, Currency, TransactionType, TransactionStatus, ExtractStatus, ReversalStatus
FROM            dbo.Reports
GO

And Sample data is:

Payment Unique Number,Request Date,Amount,Transaction Type,Transaction Status,Extract Status,Reversal Status
2654947309179233378,26/06/2021 23:59:01,13.00 QAR,Pay,2994 - Payment method selected,To be confirmed ,Reversal not required
1051819298326286815,26/06/2021 23:58:22,580.00 QAR,Pay,0000 - Payment Processed Successfully,Confirmation Acknowledged,Reversal not required

For this particular attempt, I am receiving

Cannot bulk load. Invalid column number in the format file in C:\test.fmt

Any help will be appreciated.

The Docs say the Format-file field contains "A number that indicates the position of each field in the data file. The first field in the row is 1, and so on." Seems to suggest it must be unique — SteveC
What about making the numbering unique, and making the separator after Amount a space " " — Charlieface
While asking a question, you need to provide a minimal reproducible example. Please edit your question and add the following: (1) sample of input *.csv file, (2) DDL for SQL Server target table, i.e. CREATE TABLE ... — Yitzhak Khabinsky
@booota . . . My advice is to load the data into a staging table where all the columns are strings. Then do the data manipulation as a SQL query. — Gordon Linoff
#2 is still missing: 'CREATE TABLE dbo.Reports ...`. We need to know that table structure and columns data types. — Yitzhak Khabinsky

booota booota · Accepted Answer · 2021-08-08T12:32:07

This is what I did to achieve my target, Comments will be appreciated;

14.0
7
1       SQLCHAR             0       50      ","      1     PaymentUniqueNumber                        SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR             0       50      ","      2     RequestDate                                SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             0       41      ","      3     Amount                                     ""
4       SQLCHAR             0       200     ","      5     TransactionType                            SQL_Latin1_General_CP1_CI_AS
5       SQLCHAR             0       1000    ","      6     TransactionStatus                          SQL_Latin1_General_CP1_CI_AS
6       SQLCHAR             0       1000    ","      7     ExtractStatus                              SQL_Latin1_General_CP1_CI_AS
7       SQLCHAR             0       2000    "\r\n"   8     ReversalStatus                             SQL_Latin1_General_CP1_CI_AS

and the query

SELECT
    RowSource.PaymentUniqueNumber,
    Convert(smalldatetime,RowSource.RequestDate,103) AS RequestDate,
    LTRIM(RTRIM(SUBSTRING(RowSource.Amount,0,CHARINDEX(' ',RowSource.Amount,0)))) AS Amount,
    LTRIM(RTRIM(SUBSTRING(RowSource.Amount,CHARINDEX(' ',RowSource.Amount,0)+1,LEN(RowSource.Amount)))) As Currency,
    RowSource.TransactionType,
    RowSource.TransactionStatus,
    RowSource.ExtractStatus,
    RowSource.ReversalStatus
    FROM OPENROWSET
    (BULK 'C:\test1.csv', 
FORMATFILE = 'C:\test1.fmt',
CODEPAGE = 'RAW',
FIRSTROW = 2,
MAXERRORS = 0,
ROWS_PER_BATCH = 0
) AS RowSource;

Skipped the currency column in fmt
manipulated the Currency column in select query

Bulk inserting a csv in SQL using a formatfile with 1 entry for multiple columns

2 Answers