CODEWITHSUNDEEP
pythonsql-serverpandasdatabasedataframe
Anonymous
Anonymous

Reputation: 11

Reading each line in CSV file and storing in SQL tables line by line

There is a CSV file like below, I want to read each line and if the starting letter of the is "H" then store in SQL table and if it starts with "D" then store in next different table similarly for letter starting with "T" in the file. The file should be read from NAS drive and keep iterating and inserting to respective SQL tables. below is code:

Create all tables;
then declare the path of the file;
  `df = open(path, 'r')
  Lines = df.readlines()
  count = 0
  for line in Lines:
  count += 1
  data = line.split('|')
  Create tables A, B and C
  if data[0] == "H": Then insert into table
  if data[0] == "D": Then insert into table
  if data[0] == "T": Then insert into table `

But its reading only first row and not going to next and when first table is created it says table already exists for second run though drop table table A isn't working.

H|2.1|Additional Diag|2.1|Proprietary|ASMINB|2.1||751106|TXT||20210927190057|PROD|a7a8ec06-b1bc-4961-b1b5-61203c9d1ac2|ASM PD/HDC|89|Epsilon Team||ASM_HC_EXTERNAL|CHART QA|00253|||||^

D|MR|1437662063|||UHC|c943dcff416cd893e0534499330ae568|751106-10000003-01|8DG5CE9VM53_GSQA23221504|P|124|||||1164926390|176624||DEFAULT|99|PADGETT|NATALIE|||||||||||1164926390||||||||||||||104994605||AMC|AMR;AMR|8DG5CE9VM53|HARPER|DAVID|L|04/01/1960|M|H2531|1302 RAMBLEWOOD TRL||SOUTH EUCLID|OH|44121|||08/09/2021|08/09/2021|ICD10|||E1042|CO||||99213|12|20210927190057||N||8DG5CE9VM53|8DG5CE9VM53||||^

T|751106|1|1|3|^

Upvotes: 0

Views: 441

Answers (2)

Sachin Padha
Sachin Padha

Reputation: 221

Please try data[0].strip() and check the code again.

Upvotes: 1

Filip Hanes
Filip Hanes

Reputation: 805

Your code was not properly indented. And you are missing SQL execution commands so we cannot tell if they are correct. Hope this helps:

# CREATE TABLE A IF NOT EXISTS ...
# CREATE TABLE B IF NOT EXISTS ...
# CREATE TABLE C IF NOT EXISTS ...
with open(path) as df:
    count = 0
    for line in df.readlines():
        count += 1
        data = line.split('|')
        if data[0] == "H":
            # INSERT INTO A VALUES ...
        elif data[0] == "D":
            # INSERT INTO B VALUES ...
        elif data[0] == "T":
            # INSERT INTO C VALUES ...
   print("Count:", count)

Upvotes: 0

Related Questions