0

I have a text file with two million rows:

adsh    tag version coreg   ddate   qtrs    uom value   footnote
0001065088-20-000018    DecreaseInUnrecognizedTaxBenefitsIsReasonablyPossible   us-gaap/2018        20200331    0   USD 19000000.0000   
0000885725-20-000022    DecreaseInUnrecognizedTaxBenefitsIsReasonablyPossible   us-gaap/2018        20200331    0   USD 98000000.0000   
0000837465-20-000010    DecreaseInUnrecognizedTaxBenefitsIsReasonablyPossible   us-gaap/2018        20200331    0   USD 449000.0000 
0001730168-20-000109    DecreaseInUnrecognizedTaxBenefitsIsReasonablyPossible   us-gaap/2018        20200430    0   USD 154000000.0000  
0001730168-20-000109    OperatingLeasesRentExpenseNet   us-gaap/2018        20190430    1   USD 59000000.0000   
0001730168-20-000109    OperatingLeasesRentExpenseNet   us-gaap/2018        20190430    2   USD 126000000.0000  
0001104659-20-068703    OperatingLeasesRentExpenseNet   us-gaap/2018        20180131    4   USD 19700000.0000
0001418135-20-000018    OtherAmortizationOfDeferredCharges  us-gaap/2018        20190331    1   USD 36000000.0000   Primarily includes amortization of customer rebates and upfront payments.
0001002910-20-000115    OtherAmortizationOfDeferredCharges  us-gaap/2018    UnionElectricCompany    20200331    1   USD 23000000.0000

You can see that this text file has 9 columns and, in the first rows there are only 7 columns. This is because there are other rows with 9 columns. I need to write a MySQL database with every row but I have to take into account the NULLvalues like this:

adsh                    tag                                                     version       coreg                  ddate      qtrs    uom  value          footnote
0001065088-20-000018    DecreaseInUnrecognizedTaxBenefitsIsReasonablyPossible   us-gaap/2018   NULL                  20200331   0       USD  19000000.0000  NULL
0001002910-20-000115    OtherAmortizationOfDeferredCharges                      us-gaap/2018   UnionElectricCompany  20200331   1       USD  23000000.0000  NULL

How can I make it?

Dennis
  • 79
  • 1
  • 8
  • Well, the target table should have the corresponding columns nullable. – Tarik Jul 15 '20 at 18:38
  • Yes. But when I convert the line into a string list and insert it in MySQL every cell is inserted sequentially. Column1, first cell, column2, second cell, and so on. I always get the last cells NULL which actually they are not, as you can see in the example text file. – Dennis Jul 15 '20 at 18:41
  • 1
    Well, you need to decompose your problem into parsing and inserting. You need to ensure you are correctly reading row by row ensuring you are getting nulls at the correct position. – Tarik Jul 15 '20 at 18:45
  • 1
    See this question https://stackoverflow.com/questions/4914008/how-to-efficiently-parse-fixed-width-files – Tarik Jul 15 '20 at 18:48
  • Thank you! This is what I was looking for. – Dennis Jul 15 '20 at 18:51
  • Why do (most) question askers think that anyone is *impressed* when they start their question with "a text file with two million rows"...? – Luuk Jul 15 '20 at 18:58

1 Answers1

0

with open ("filename.txt", 'r') as nametxt; Text = (nametext (read()); Print (Text)

Something like that

H Gaming
  • 58
  • 8