-3

I'm trying to insert data from an xlsx file into my temporal table. I've also tried with a real table, but it throws me the following error:

(0 rows affected)

I'm pretty sure my error is in the bulk insert part, because when I run just that part it throws me the same error.

This is the statement I am using to insert the data:

IF OBJECT_ID('tempdb..#stats_ddl') is not null DROP TABLE #stats_ddl 
GO
CREATE TABLE #stats_ddl ([Fecha] varchar(50),
[Descripción] varchar(max),
[Depósitos] varchar(50),
[Retiros] varchar(50),
[Saldo] varchar(50));
Go 

bulk insert mydb.dbo.#stats_ddl
from 'C:\some\where\myfile\is\file.xlsx'
with (          firstrow = 14,                    
            FIELDTERMINATOR = '\t',
            ROWTERMINATOR = '\n')

I am receiving this error:

Database name 'mydb' ignored, referencing object in tempdb.

What issues am I facing?

  • 3
    Temporal tables are not the same as temporary tables. Do you mean "temporal" or "temporary"? – Max Vernon Aug 21 '18 at 18:25
  • Try testing your field and row terminators. Like open in Notepad++ and turn on all chars and verify that you are actually having tabs and CR&LF for your terminators. – Jacob H Aug 21 '18 at 18:43
  • 2
    It looks strange to import a .xlsx file with settings for a TSV. – eckes Aug 21 '18 at 18:45
3

You cannot specify a database name, or schema name, when using a #temporary table.

Remove the # from the name of the stats_ddl table, and you'll see it works.

If you want to insert the rows into a temporary table, then remove the reference to the database name:

IF OBJECT_ID('tempdb..#stats_ddl') is not null DROP TABLE #stats_ddl 
GO
CREATE TABLE #stats_ddl ([Fecha] varchar(50),
[Descripción] varchar(max),
[Depósitos] varchar(50),
[Retiros] varchar(50),
[Saldo] varchar(50));
Go 

bulk insert #stats_ddl
from 'C:\some\where\myfile\is\file.xlsx'
with (          firstrow = 14,                    
            FIELDTERMINATOR = '\t',
            ROWTERMINATOR = '\n')
  • When I run it that way throws > (0 rows affected) – E.Rawrdríguez.Ophanim Aug 21 '18 at 18:19
  • 4
    Which is completely different from Database name 'mydb' ignored, referencing object in tempdb. – Max Vernon Aug 21 '18 at 18:22
  • I agree, but it's not helping me out – E.Rawrdríguez.Ophanim Aug 21 '18 at 18:25
  • 6
    That indicates there is a problem with the xlsx file, not the temp table. – Max Vernon Aug 21 '18 at 18:27
  • 2
    As noted in comments on the question, you've provided field and row terminators that match up to a tab-separated values file. To upload it that way, you need to save the Excel file as a tab-delimited text file. To use the Excel file directly, you need to use something like OPENDATASOURCE or OPENROWSET (see here for examples. – RDFozz Aug 21 '18 at 20:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.