Floats converting to symbols reading from .dat file. Unsure of encoding

user_6274595 0
Viewed 580

I am attempting to read MapInfo .dat files into .csv files using Python. So far, I have found the easiest way to do this is though xlwings and pandas.

When I do this (below code) I get a mostly correct .csv file. The only issue is that some columns are appearing as symbols/gibberish instead of their real values. I know this because I also have the correct data on hand, exported from MapInfo.

import xlwings as xw
import pandas as pd

app = xw.App(visible=False)

tracker = app.books.open('./cable.dat')


last_row = xw.Range('A1').current_region.last_cell.row

data = xw.Range("A1:AE" + str(last_row))


test_dataframe = data.options(pd.DataFrame, header=True).value

test_dataframe.columns = list(schema)
test_dataframe.to_csv('./output.csv')

The float columns in the output csv

When I compare to the real data, I can see that the symbols do actually map the correct number (meaning that (1 = ­?, 2=@, 3=@, etc.)

Below is the first part of the 'dictionary' as to how they map: My dictionary

My question is this: Is there an encoding that I can use to turn these series of symbols into their correct representation? The floats aren't the only column affected by this, but they are the most important to my data.

Any help is appreciated.

xlwings pandas encoding python
2 Answers
user_54783730

EDIT

If I understand well you are using XLWings to load the .dat file into excel. And then read it into pandas dataframe to export it into a csv file. Somewhere along this it seems indeed that some binary data is not/incorrectly interpreted and then written as text to you csv file.

directly read dBase file

My first suggestion would be to try to read the input file directly into Python without the use of an excel instance.

According to Wikipedia, mapinfo .dat files are actually are dBase III files. These you can parse in python using a library like dbfread.

inspect data before writing to csv

Secondly, I would inspect the 'corrupted' columns in python instead of immediately writing them to disk.

  • Either something is going wrong in the excel import and the data of these columns gets imported as text instead of some binary number format,
  • Or this data is correctly into memory as a byte array (instead of a float), and when you write it to csv, it just gets byte-wise dumped to disk instead of interpreting it as a number format and making a text representation of it

note

Small remark about your initial question regarding mapping text to numbers: Probably it will not be possible create a straightforward map of characters to numbers:

  • These numbers could have any encoding and might not be stored as decimal text values like you now seem to assume
  • These text representations are just a decoding using some character encoding (UTF-8, UTF-16). E.g. for UTF-8 several bytes might map to one character. And the question marks or squares you see, might indicate that one or more characters could not be decoded.
  • In any case you will be losing information if start from the text, you must start from the binary data to decode.
user_101557400
Related