I have a csv file that has inconsistent quotes throughout. Whenever a certain column has a thousands seperator it surrounds the column with quotes, but when it has no comma it does not. The bulk import feature of SQL Server 2008 cannot handle this. I need a solution that takes the import of a csv file and loads it to my sql table correctly. I will include a script of the table and a sample CSV file.
I have tried the SQL command BULK IMPORT, but it cannot handle the inconsistent quotes.
I would be ok with a php page solution like this: [login to view URL]
CREATE TABLE [dbo].[StagingTransactions](
[RefID] [varchar](50) NOT NULL,
[CheckIn] [varchar](50) NULL,
[CheckOut] [varchar](50) NULL,
[NumberofNights] [varchar](50) NULL,
[PaymentDate] [varchar](50) NULL,
[PaymentType] [varchar](50) NULL,
[PropertyID] [varchar](50) NULL,
[GuestName] [varchar](50) NULL,
[PaymentMethod] [varchar](50) NULL,
[TaxableRevenue] [varchar](50) NULL,
[NonTaxableRevenue] [varchar](50) NULL,
[Tax] [varchar](50) NULL,
[PaidByGuest] [varchar](50) NULL,
[TransactionFee] [varchar](50) NULL,
[DepositAmount] [varchar](50) NULL,
[Currency] [varchar](50) NULL,
[ReservationID] [varchar](1100) NULL