Date Type Issue on Datetime and Text Using BulkInsert

Question

I have a dataset more than 1000 when import into SSMS. Thus I use the following T-SQL to create table and import data:

CREATE TABLE sales(
  SPID text,
  GeoID text,
  PID text,
  SaleDate smalldatetime,
  Amount int,
  Customers int,
  Boxes int);

BULK INSERT sales
FROM 'C:\Users\alvin_zoj6s4b\Downloads\sales.txt'
WITH (FIELDQUOTE = '\' 
      , FIELDTERMINATOR = ','
      , ROWTERMINATOR = '\n');

I got the following error:

The problem is on the SalesDate column so I change from smalldatetime to Text on create table query:

CREATE TABLE sales(
  SPID text,
  GeoID text,
  PID text,
  SaleDate TEXT,
  Amount int,
  Customers int,
  Boxes int);

The result of the table showing ' ' on the text data type (SPID, GeoID, PID & SaleDate):

Here is the structure of my text file stored in my laptop:

The 2 problems I would like to clarify:

1. Text data type should put ' ' in the raw data when import the data to SSMS but why my output will showing ' '?
2. The SaleDate only work when I change it to text, anything I have done wrongly?

Answer 1

As I mentioned in the comments, you need to tell SQL Server that the file is a CSV file, with FORMAT and tell it the quote identifier is a single quote (') with FIELDQUOTE, as the default is a double quote ("). You had, for some reason, defined the FIELDQUOTE as '\', yet there isn't a single \ in your images.

You also need to fix those data types; text has been deprecated for 17 years and so you shouldn't be using it. I have used lengths based on your image of data (Please do not upload images of code/data/errors when asking a question.). I also use a date for your column SaleDate as all the times are at midnight; if they do have a time portion, then you might want a datetime2 (as there's no milliseconds in the data a datetime2(0) would make sense).

This gives the following:

CREATE TABLE dbo.sales (SPID varchar(4), --guessed length, but I doubt you need MAX for values that will have 8,000+ characters
                        GeoID varchar(2), --guessed length, but I doubt you need MAX for values that will have 8,000+ characters
                        PID varchar(3), --guessed length, but I doubt you need MAX for values that will have 8,000+ characters
                        SaleDate date, --Considering all your columns are mid night, this seems a better choice. Otherwise use datetime2(0)
                        Amount int,
                        Customers int,
                        Boxes int);

GO
BULK INSERT dbo.sales
FROM 'C:\Users\alvin_zoj6s4b\Downloads\sales.txt'
WITH (FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n',
      FORMAT = 'CSV',
      FIELDQUOTE = '''');