Importing this CSV from a URL only gets 13 rows, not 596

[simonw]

https://raw.githubusercontent.com/okfn/dataportals.org/master/data/portals.csv

The "Open CSV from URL..." menu option only produced 13 rows - but using sqlite-utils insert portals.db portals portals.csv --csv on the command-line got all 596.

[simonw]

Still a bug against latest Datasette Desktop release.

[simonw]

Here's how that CSV file starts:

And in Datasette the data cuts off here:

Which is right where the first double-newline paragraph break in that CS file occurs.

[simonw]

This is a datasette-app-support problem, moving the issue there.

[simonw]

I'm suspicious of this code:

datasette-app-support/datasette_app_support/utils.py

Lines 69 to 71 in d130884

	async with httpx.AsyncClient() as client:
	async with client.stream("GET", url, follow_redirects=True) as response:
	reader = AsyncDictReader(response.aiter_lines())

Maybe that AsyncDictReader(response.aiter_lines()) pattern can't cope with CSV files that include their own double-quoted newlines?

[simonw]

This code is also relevant:

datasette-app-support/datasette_app_support/utils.py

Lines 17 to 49 in d130884

	class AsyncDictReader:
	def __init__(self, async_line_iterator):
	self.async_line_iterator = async_line_iterator
	self.buffer = io.StringIO()
	self.reader = DictReader(self.buffer)
	self.line_num = 0
	def __aiter__(self):
	return self
	async def __anext__(self):
	if self.line_num == 0:
	header = await self.async_line_iterator.__anext__()
	self.buffer.write(header)
	line = await self.async_line_iterator.__anext__()
	if not line:
	raise StopAsyncIteration
	self.buffer.write(line)
	self.buffer.seek(0)
	try:
	result = next(self.reader)
	except StopIteration as e:
	raise StopAsyncIteration from e
	self.buffer.seek(0)
	self.buffer.truncate(0)
	self.line_num = self.reader.line_num
	return result

[simonw]

Here are my notes from when I wrote that AsyncDictReader class: #14 (comment)

[simonw]

Maybe AsyncDictReader.__anext__() needs to be smart enough to watch out for unbalanced double quotes and consume another line if it spots one?

[simonw]

https://github.com/MKuranowski/aiocsv may be able to handle this for me.

[simonw]

aiocsv is designed to work with a aiofiles object with a .read() coroutine - I'm not sure how best to map that to an httpx streaming response.

[simonw]

I'm beginning to think it would be better for the app to either suck the entire CSV file into memory OR to save it to a temporary file on disk, then read it into a table. Much simpler that way - this problem with newlines has made me very suspicious of importers that don't directly use csv as it was intended to be used.

[simonw]

I'm going to go with the memory option. Datasette Desktop runs on Macs with a decent amount of RAM, and with swap.