Convert Text to Excel

Question

I have the following text file as input

Patient Name:       XXX,A

Date of Service:    12/12/2018

Speaker ID:     10531
Visit Start:    06/07/2018
Visit End:      06/18/2018
Recipient:      
REQUESTING PHYSICIAN:
Mr.XXX

REASON FOR CONSULTATION:
Acute asthma.

HISTORY OF PRESENT ILLNESS:
The patient is a 64-year-old female who is well known to our practice.  She has not been feeling well over the last 3 weeks and has been complaining of increasing shortness of breath, cough, wheezing, and chest tightness.  She was prescribed systemic steroids and Zithromax.  Her respiratory symptoms persisted; and subsequently, she went to Capital Health Emergency Room.  She presented to the office again yesterday with increasing shortness of breath, chest tightness, wheezing, and cough productive of thick sputum.  She also noted some low-grade temperature.

PAST MEDICAL HISTORY:
Remarkable for bronchial asthma, peptic ulcer disease, hyperlipidemia, coronary artery disease with anomalous coronary artery, status post tonsillectomy, appendectomy, sinus surgery, and status post rotator cuff surgery.

HOME MEDICATIONS:
Include;
1.  Armodafinil.
2.  Atorvastatin.
3.  Bisoprolol.
4.  Symbicort.
5.  Prolia.
6.  Nexium.
7.  Gabapentin.
8.  Synthroid.
9.  Linzess_____.
10.  Montelukast.
11.  Domperidone.
12.  Tramadol.

ALLERGIES:
1.  CEPHALOSPORIN.
2.  PENICILLIN.
3.  SULFA.

SOCIAL HISTORY:
She is a lifelong nonsmoker.

PHYSICAL EXAMINATION:
GENERAL:  Shows a pleasant 64-year-old female.
VITAL SIGNS:  Blood pressure 108/56, pulse of 70, respiratory rate is 26, and pulse oximetry is 94% on room air.  She is afebrile.
HEENT:  Conjunctivae are pink.  Oral cavity is clear.
CHEST:  Shows increased AP diameter and decreased breath sounds with diffuse inspiratory and expiratory wheeze and prolonged expiratory phase.
CARDIOVASCULAR:  Regular rate and rhythm.
ABDOMEN:  Soft.
EXTREMITIES:  Does not show any edema.

LABORATORY DATA:
Her INR is 1.1.  Chemistry; sodium 139, potassium 3.3, chloride 106, CO2 of 25, BUN is 10, creatinine 0.74, and glucose is 110.  BNP is 40.  White count on admission 16,800; hemoglobin 12.5; and neutrophils 88%.  Two sets of blood cultures are negative.  CT scan of the chest is obtained, which is consistent with tree-in-bud opacities of the lung involving bilateral lower lobes with patchy infiltrate involving the right upper lobe.  There is mild bilateral bronchial wall thickening.

IMPRESSION:
1.  Acute asthma.
2.  Community acquired pneumonia.
3.  Probable allergic bronchopulmonary aspergillosis.
   

I want the text file to be converted as an excel file

 Patient Name   Date of Service Speaker ID  Visit Start Visit End   Recipient ..... IMPRESSION:
                        
     XYZ        2/27/2018      10101       06-07-2018   06/18/2018   NA    .......   1.  Acute asthma.
                                                                                     2.  Community 
                                                                                         acquired 
                                                                                         pneumonia.
                                                                                     3.  Probable 
                                                                                          allergic     

I wrote the following code

with open('1.txt') as infile:
    registrations = []
    fields = OrderedDict()
    d = {}
    for line in infile:
        line = line.strip()
        if line:
            key, value = [s.strip() for s in line.split(':', 1)]
            d[key] = value
            fields[key] = None
        else:
            if d:
                registrations.append(d)
                d = {}
    else:
        if d:    # handle EOF
            registrations.append(d)

    with open('registrations.csv', 'w') as outfile:
    writer = DictWriter(outfile, fieldnames=fields)
    writer.writeheader()
    writer.writerows(registrations)

I'm getting an error

ValueError: not enough values to unpack (expected 2, got 1)

I'm not sure what the error is saying. I searched through websites but could not find a solution. I tried editing the file to remove the space and tried the above code, it was working. But in real-time scenario there will be hundreds of thousands of files so manually editing every file to remove all the spaces is not possible.

Answer 1

Your particular error is likely from

key, value = [s.strip() for s in line.split(':', 1)]

Some of your lines don't have a colon, so there is only one value in your list, and we can't assign one value to the pair key, value.

For example:

line = 'this is some text with a : colon'
key, value = [s.strip() for s in line.split(':', 1)]
print(key)
print(value)

returns:

this is some text with a

colon

But you'll get your error with

line = 'this is some text without a colon'
key, value = [s.strip() for s in line.split(':', 1)]
print(key)
print(value)