Exporting csv with linked tables

Anvil Q&A
1

Is there a way to get a csv file with “Human readable” values from a table with linked rows, rather than the #ROW[random numbers that make the accountant mad] values? I have a button that does a search and pulls out selected data from a table and the resulting csv file looks like this:

7/1/2019, #ROW[24748,2766961], #ROW[21924,2829641]

and I am trying to get the values behind the row id’s
7/1/2019, ABCLawn Care, Greenwaste

if anyone could help that would be amazing

Sean

1 Like
2

Hello @sean and welcome to the forum.

This is a good question.

AFAIK the only way to represent your linked columns in a human-readable manner is to construct your own CSV, to your liking, from the related data tables.

For example, you can loop through the rows from your main table, store the information you want as a string (even from the linked rows), and use that string to create an Anvil Media object. The Media object can then be downloaded as a CSV.

Here is an example where I have two tables: Orders and Products. You can see that the code loops through each order and pulls out data from the linked Product table (product name, and product department). It then turns everything into a string (serializes) so that it can be made into a downloadable Media object.

# in a server module

@anvil.server.callable
def get_media():
  
  my_string=""
  for row in app_tables.orders.search():
    
    order_date=row['date'].strftime("%B %d, %Y").replace(',','') # some formatting
    order_name=row['product']['name'] # linked row
    product_dept=row['product']['department'] # linked row
    
    record=','.join([str(order_date),str(order_name),str(product_dept)])+'\n'
    my_string+=record
  
  media=anvil.BlobMedia('text/csv',my_string,name='my_new.csv')
  
  return media

On the client side, a button is set to trigger the download:

def button_1_click(self, **event_args):
  """This method is called when the button is clicked"""

  m=anvil.server.call('get_media')
  download(m)

Here’s a clone of the app so you can try it for yourself:

I shamelessly stole this approach from this thread (thanks @david.wylie) :

Your code will look different of course, especially if you want to extract all of your columns, but this is just an example to give you the gist.

There may be a better way, but it’s late and this is the best I could come up with. Good luck!

1 Like
3

That seems like it would be a lot of processing each time the report is pulled. Maybe I am coming at this from the wrong end. Maybe would be better to find a way to store the data in a table in readable form from the beginning.

The form that I get the data from has several drop down lists for the user to pick values from (the dropdowns pull from a datatable). And I am using self.company_dd.selected value is there a way to convert the values at that point to a text string and just store it as the text value?

4

Yes, if you don’t need linked rows, you could consider not using them. The selected values in your drop down menus can be stored in your main data table as you would do normally (see docs for examples).

Then you can just return a media file to the client and download it by using:

media=app_tables.my_table.search().to_csv()

Since there are no linked rows, the CSV export should look good.

1 Like
5

After realizing that the server call is only processing the data from the query it makes sense to just to stick with the linked tables in this case.

I got the server callable created but when it goes to create the string I get an error …UnicodeEncodeError: ‘ascii’ codec can’t encode character u’\u2019’ in position 6: ordinal not in range(128)

this is using data from the Anvil Data Tables Service …

This is what my server callable looks like
@anvil.server.callable
def get_media(date,endD):
my_string=""
for row in app_tables.trucklog.search(Date=q.between(date,endD,max_inclusive=True)):
Date=row[‘Date’]
Dispatch_txt=row[‘Dispatch’][‘Name’]
Company_txt=row[‘Company’][‘name’]
TruckNumber=[‘TrucknNumber’]
Comments=row[‘Comments’]
Material_txt=row[‘Material’][‘material’]
Time=row[‘Time’].strftime("%B %d, %Y").replace(’,’,’’)
record=’,’.join([str(Date),str(Dispatch_txt),str(Company_txt),str(TruckNumber),str(Comments),str(Material_txt),str(Time)])+‘n’
my_string+=record
m=anvil.BlobMedia(‘text/csv’,my_string,name=‘WeekLog.csv’)
return m

it gets hung up on the record=’,’.join([str(Date)… line

6

Hi there.

It is a good idea to share a clone link to your application if possible (a small example to demonstrate the issue). This makes it much easier to debug. Click the :gear: icon and follow the sharing instructions. Also you can format your code in the forum by wrapping your code in triple backticks, for example:

```python
print(‘nicely formatted code’)
```

One thought is that you may need the u prefix for any non-ASCII string that you are using. Please see here for a possibly related issue, and here from a quick StackOverflow search.

Also, you are missing the newline ('\n') character in your str concatenation, although I don’t think this was the reason for your error above.

1 Like
7

Thank you so much for your help!
I was able to work out the issue using the info you gave me… I ended up having to add .encode(‘utf-8’) to each one that was a linked table.
This is what the end code looks like:

@anvil.server.callable
def get_media(date,endD):
  my_string=""
  for row in app_tables.trucklog.search(Date=q.between(date,endD,max_inclusive=True)):
    Date=row['Date']
    DateTime_txt=row['Time'].strftime("%I:%M %p").replace(',','')
    Dispatch_txt=row['Dispatch']['Name'].encode('utf-8').replace(',','')
    Company_txt=row['Company']['name'].encode('utf-8').replace(',','')
    TruckNumber_txt=row['TruckNumber']
    Comments=row['Comments']
    Material_txt=row['Material']['material'].encode('utf-8')
    record=','.join([str(Date),str(Dispatch_txt),str(Company_txt),str(TruckNumber_txt),str(Comments),str(Material_txt),str(DateTime_txt)])+'\n'
    my_string+=record
    m=anvil.BlobMedia('text/csv',my_string,name='DataLog.csv')
  return m
2 Likes