Read csv file without headers and store each column into different sql table

Question

I have csv file without headers and each column has different number of rows, like first column as H and second as D 3rd as G. I want to read the csv and store H in one table and D in other table and F in third table Using PYTHON Visual code. File is like this--

H|22|erewr|2.1|Asdsds|ASMINB|2.1||385904|Filename.TXT||20254545203|TEST|weerer-ertret-ret|FEE

D|MR|2343444|23296d26-a826-409d-bfd3-55ae09e0178f||UHC|423434fssdf|385904-10002465-01|1804874425|P|124|||||5555645961|176703||DEFAULT|99|||||||||||||565465||||||||||||||11||P||34ryyr4|6rrtytr||||^

F|MR|1670111520|234324-reert433-3334||ADC|erwerewr|

Why your separator is '|' ? You considered to first line as a "column" ? I'd say that the first column is ['H', 'D', 'F'] — ArrowRise
– ArrowRise, Commented Jul 20, 2022 at 12:26
Yes first column is ['H', 'D', 'F'] and each column has different number of rows. and each column starting with ['H', 'D', 'F'] has to load to three different sql tables with their respective rows — Anonymous
– Anonymous, Commented Jul 20, 2022 at 14:49

Edo Akse · Accepted Answer · 2022-07-21 13:52:01Z

0

So the process itself is relatively simple.

Open the file.
Iterate over the lines.
Get the first element in the line.
Based on the value, append to a list.

EXAMPLE

splitchar = "|"
table_h = []
table_d = []
table_f = []

with open("somefile.csv") as infile:
    # the method below is OS agnostic, and removes the EOL character.
    for line in infile.read().splitlines():
        table = line.split(splitchar)[0]
        values_to_write = line.split(splitchar)[1:]
        if table == "H":
            table_h.append(values_to_write)
        elif table == "D":
            table_d.append(values_to_write)
        elif table == "F":
            table_f.append(values_to_write)
        # continue with elif selections here if needed

        else:
            #always nice to have a catchall
            print(f"Did not find a table to write to.\n  Table name == {table}\n  Values: {values_to_write}")

answered Jul 21, 2022 at 13:52

Edo Akse

4,4052 gold badges13 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Anonymous Over a year ago

Thanks for the reply. But I m trying to access the file from nas drive and it should iterate for the files in that folder also and store in SQL tables wrt the first letters in csv file.

Edo Akse Over a year ago

well the obvious thing would be to put that in your question. You're going to get the answer to the question you actually ask, as we cannot read your mind...

Collectives™ on Stack Overflow

Read csv file without headers and store each column into different sql table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related