• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 588
  • Last Modified:

how do i load a txt file into sql server database

i have a text file in which the files are in the following format (see attached text file)
now the original text file is 233 mb big

now I need to load that text file, without the surrounding inverted commas ("") on each file
how do i do it? so that when I load it, the data is in sync with the data types in the table

don't ask me to replace the inverted commas in the text file.
CREATE TABLE zztbNetworkSettlementExtract
( 
    ChargeCode INT
    ,TransactionNumber VARCHAR(50) 
    ,NoOfCharges INT
    ,PreviousTransactionNumber VARCHAR(50)
    , TransactionDate DATETIME
    ,ChargeType VARCHAR(10)
    ,ChargeDescription VARCHAR(50)
    ,NMI VARCHAR(10) 
    ,CheckSumValue INT	
    ,TariffCode VARCHAR(3)
    , Note VARCHAR(250)
    , ChargeStartDate DATETIME
    , ChargeEndDate DATETIME
    , TariffCodeDesc VARCHAR(250)
    , ReadType VARCHAR(2)
    , TariffDescription VARCHAR(50)
    , Consumption FLOAT
    , UOM VARCHAR(4)
    , Rate FLOAT	
    , GSTExlAmt FLOAT	
    , GSTAmt FLOAT	
    , GST VARCHAR(1)
)
 
 
---
 
BULK INSERT zztbNetworkSettlementExtract
    FROM 'c:\file.txt' 
    WITH 
    ( 
        FIELDTERMINATOR = ',', 
        ROWTERMINATOR = '\n' 
    )

Open in new window

testNuo.txt
0
manivineet
Asked:
manivineet
  • 2
1 Solution
 
rob_farleyCommented:
Try running the Import/Export Wizard. Go to Management Studio, right-click on your database, and choose "Import Data" from the Tasks menu.

There are plenty of options in there for handling the speech-marks.

Rob
0
 
RiteshShahCommented:
have a look at screen shot.
1.JPG
2.JPG
3.JPG
4.JPG
5.JPG
6.JPG
0
 
RiteshShahCommented:
once you have "TestNUO" table in SQL Server you can do something like below.

insert into zztbNetworkSettlementExtract
select REPLACE([column 0],'"',''),REPLACE([column 1],'"','').............from testnuo
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now