<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Not so clean text to tables (pandas fails)
An example where import_flatfile_into_database does a better job than pandas about converting a flat file to a table.
Step1:  Le's assume now we introduce extra tabulations.
Step2:  It works well because we use pandas to save the dataframe, and we use pandas to restore it. In the file flatfile_tab.txt, it looks like "on\te". pandas interprets the quotes as a delimiter. However most of the times, the flat file is produced in a different way and the quotes are not present.
Step3:  It failed! Data is not aligned and it did not raise an exception. If we move the extra tab in second position, we get
Step4:  As suggested in Python Pandas Error tokenizing data, we could add the parameter error_bad_lines=False or skiprows=N but we would still lose those bad lines. So we use function import_flatfile_into_database.
Step5:  We check that we got the inserted line in the dataframe
<ASSISTANT_TASK:>
Python Code:
import random, pandas
text = [ "one","two","three","four","five","six","seven","eight","nine","ten" ]
data = [ { "name": text[random.randint(0,9)], "number": random.randint(0,99)} \
                        for i in range(0,10000) ]
df = pandas.DataFrame(data)
df.head(n=3)
df.to_csv("flatfile.txt", sep="\t", encoding="utf8", header=True, index=False)
dfr = pandas.read_csv("flatfile.txt", sep="\t", encoding="utf8")
dfr.head(n=3)
Explanation: Not so clean text to tables (pandas fails)
An example where import_flatfile_into_database does a better job than pandas about converting a flat file to a table.
End of explanation
datatab = [ {"name": " one\ttab", "number":100 } ] + data
df = pandas.DataFrame(datatab)
df.head(n=3)
df.to_csv("flatfile_tab.txt", sep="\t", encoding="utf8", header=True, index=False)
dfr = pandas.read_csv("flatfile_tab.txt", sep="\t", encoding="utf8")
dfr.head(n=3)
Explanation: Le's assume now we introduce extra tabulations.
End of explanation
with open("flatfile_tab.txt", "r", encoding="utf8") as f:
    content = f.read()
content = content.replace('"','')
with open("flatfile_tab2.txt", "w", encoding="utf8") as f:
    f.write(content)
dfr = pandas.read_csv("flatfile_tab2.txt", sep="\t", encoding="utf8")
dfr.head(n=3)
Explanation: It works well because we use pandas to save the dataframe, and we use pandas to restore it. In the file flatfile_tab.txt, it looks like "on\te". pandas interprets the quotes as a delimiter. However most of the times, the flat file is produced in a different way and the quotes are not present.
End of explanation
datatab = data[:1] + [ {"name": " one\ttab", "number":100 } ] + data[1:]
df = pandas.DataFrame(datatab)
df.to_csv("flatfile_tab_pos2.txt", sep="\t", encoding="utf8", header=True, index=False)
with open("flatfile_tab_pos2.txt","r",encoding="utf8") as f: 
    content = f.read()
content = content.replace('"','')
with open("flatfile_tab_pos2.txt","w",encoding="utf8") as f:
    f.write(content)
dfr = pandas.read_csv("flatfile_tab_pos2.txt", sep="\t", encoding="utf8")
dfr.head(n=3)
Explanation: It failed! Data is not aligned and it did not raise an exception. If we move the extra tab in second position, we get:
End of explanation
from pyensae.sql import import_flatfile_into_database
import_flatfile_into_database("flatfile_tab_pos2.db3", "flatfile_tab_pos2.txt")
Explanation: As suggested in Python Pandas Error tokenizing data, we could add the parameter error_bad_lines=False or skiprows=N but we would still lose those bad lines. So we use function import_flatfile_into_database.
End of explanation
from pyensae.sql import Database
db = Database("flatfile_tab_pos2.db3")
db.connect()
df = db.to_df("SELECT * FROM flatfile_tab_pos2")
db.close()
df.head()
Explanation: We check that we got the inserted line in the dataframe:
End of explanation
<END_TASK>